﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;

using System.Security.Cryptography;
using System.Net;
using System.IO;

using WebApplication1.MSG;

namespace WebService1
{
    class MedEq
    {
       



      
        public DataSet HSP_Service_list(string HSPID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            DataBase2 db2 = new DataBase2();

           
            DataSet dstemp = new DataSet();
            string selectStr = "select ID,工程师ID,工程师ID,工程师ID,工程师ID,工程师ID,工程师ID,工程师ID FROM 医院服务工程师表 WHERE convert(nvarchar(255),医院ID)= '" + HSPID + "' order by ID desc";
            ds = db.RunProcReturnDS(selectStr);
            db.Close();
            if (ds.Tables[0].Rows.Count > 0)


            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {

                    string selectStr2 = "select ChineseName,Company,Remark,Name,Major,head_image,UserLevel from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][1].ToString() + "'";
                    dstemp = db2.RunProcReturnDS(selectStr2);

                    ds.Tables[0].Rows[i][1] = dstemp.Tables[0].Rows[0][0].ToString();
                    ds.Tables[0].Rows[i][2] = dstemp.Tables[0].Rows[0][1].ToString();
                    ds.Tables[0].Rows[i][3] = dstemp.Tables[0].Rows[0][2].ToString();
                    ds.Tables[0].Rows[i][4] = dstemp.Tables[0].Rows[0][3].ToString();
                    ds.Tables[0].Rows[i][5] = dstemp.Tables[0].Rows[0][4].ToString();
                    ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][5].ToString();
                    ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][6].ToString();

                }

                ds.Tables[0].Columns[1].ColumnName = "中文名";
                ds.Tables[0].Columns[2].ColumnName = "公司";
                ds.Tables[0].Columns[3].ColumnName = "备注";
                ds.Tables[0].Columns[4].ColumnName = "UserName";
                ds.Tables[0].Columns[5].ColumnName = "专业";
                ds.Tables[0].Columns[6].ColumnName = "头像";
                ds.Tables[0].Columns[7].ColumnName = "用户等级";
                ds.AcceptChanges();
          
            
            
            }






            return ds;


        }




      

        public string Login(string username, string psw)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 用户ID,密码 from 用户表 where convert(nvarchar(255),用户ID)= '" + username + "'";

            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0][0].ToString().Trim() == username.ToString().Trim() && ds.Tables[0].Rows[0][1].ToString().Trim() == psw.ToString().Trim())


                    return "1";
                else
                    return "0";
            }
            else
                return "2";
            //1 登陆成功 0 密码错误 2 无此用户

        }


        public string Update_Android_Flag(string username)
        {

            DataBase dbs = new DataBase();
            string selectStr = "update Users set android='1'  where convert(nvarchar(255),Name)= '" + username + "'";
            return dbs.RunProc(selectStr).ToString();
            


        }

        public string Update_User_Token(string username,string token)
        {

            DataBase dbs = new DataBase();
            string selectStr = "update Users set Token='" + token + "'  where convert(nvarchar(255),Name)= '" + username + "'";
            return dbs.RunProc(selectStr).ToString();



        }






        public string Get_APP_Ver()
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select * from APP配置 ";

            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows[0][1].ToString();

        }


        public DataSet Get_User_Roles(string userID)

        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select * from RoleUsers where convert(nvarchar(255),UserID)= '" + userID + "'";

            ds = db.RunProcReturnDS(selectStr);

            return ds;
        
        
        }











        public DataSet Get_Technician_Info(string UserName)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select Users.ID,Users.Name,Users.ChineseName,Users.Password,RoleUsers.RoleID,Users.CellPhone,Users.DeptID from Users  JOIN RoleUsers ON  convert(nvarchar(255),Users.ID)= convert(nvarchar(255),RoleUsers.UserID) where convert(nvarchar(255),Users.Name)= '" + UserName + "' order by RoleUsers.RoleID ";
          



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Get_User_Info(string UserName)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select Users.ID,Users.Name,Users.ChineseName,Users.Password,RoleUsers.RoleID,Users.CellPhone,Users.DeptID from Users  JOIN RoleUsers ON  convert(nvarchar(255),Users.ID)= convert(nvarchar(255),RoleUsers.UserID) where convert(nvarchar(255),Users.Name)= '" + UserName + "' order by RoleUsers.RoleID ";




            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Get_Service_EGN_Info(string UserID)
        {
            DataBase2 db = new DataBase2();

            DataSet ds = new DataSet();

            string selectStr = "select ID,ChineseName,Company,Remark,Name,Major,head_image,UserLevel from Users where convert(nvarchar(255),ID)= '" + UserID + "'";
                  
            



            ds = db.RunProcReturnDS(selectStr);
            ds.Tables[0].Columns[1].ColumnName = "中文名";
            ds.Tables[0].Columns[2].ColumnName = "公司";
            ds.Tables[0].Columns[3].ColumnName = "备注";
            ds.Tables[0].Columns[4].ColumnName = "UserName";
            ds.Tables[0].Columns[5].ColumnName = "专业";
            ds.Tables[0].Columns[6].ColumnName = "头像";
            ds.Tables[0].Columns[7].ColumnName = "用户等级";
            ds.AcceptChanges();
            return ds;


        }








        public string  Get_Chinef_Hospital(string HSPID)
        {


            DataBase db = new DataBase();
           
            DataSet ds = new DataSet();

            string selectStr = "select Users.Name from Users  JOIN RoleUsers ON  convert(nvarchar(255),Users.ID)= convert(nvarchar(255),RoleUsers.UserID) JOIN Depts ON convert(nvarchar(255),Users.DeptID)= convert(nvarchar(255),Depts.DeptNumber) where convert(nvarchar(255),RoleUsers.RoleID)= '10' AND convert(nvarchar(255),Depts.ParentID)= '" + HSPID + "' order by Users.ID ";
        


            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0][0].ToString();
            else
                return "nochief";


        }

    









        public string Get_HSP_By_DeptID(string DeptID)
        {

            DataBase db = new DataBase();
            DataSet ds = new DataSet();
            string selectStr = "select ParentID from Depts where ID='" + DeptID + "'";
            ds = db.RunProcReturnDS(selectStr);

            return ds.Tables[0].Rows[0][0].ToString();


        }

       
        public string Get_User_Token(string UserName)
        {

            DataBase db = new DataBase();
            DataSet ds = new DataSet();
            string selectStr = "select Token from Users where Name='" + UserName + "'";
            ds = db.RunProcReturnDS(selectStr);

            return ds.Tables[0].Rows[0][0].ToString();


        }



        public string DEL_Task_By_Flag(string flag)
        {

            DataBase dbs = new DataBase();
            string selectStr = "delete from 报修任务表 where Flag='" + flag + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;


        }
        public string DEL_User_Dept(string UserID, string DeptID)
        {

            DataBase dbs = new DataBase();
            string selectStr = "delete from 负责科室表 where UserID='" + UserID + "' and DeptID='" + DeptID + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;


        }
      

        public string Serv_DEL_EQ_By_Flag(string flag)
        {

            DataBase dbs = new DataBase();
            string selectStr = "delete from 设备表 where Flag='" + flag + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;


        }


        public string DEL_Repair_Log(string flag, string Hospital)
        {

            DataBase dbs = new DataBase();
            string selectStr = "delete from 维护记录表 where Flag='" + flag + "'AND convert(nvarchar(255),所属医院)= '" + Hospital + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;


        }

        public string DEL_Repair(string flag, string Hospital)
        {

            DataBase dbs = new DataBase();
            string selectStr = "delete from 任务表 where Flag='" + flag + "'AND convert(nvarchar(255),所属医院)= '" + Hospital + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;


        }


        public string Update_Repair_Log(string Flag, string RP_Flag, string Photocount, string Voicecount, string Content,
            string Recordperson, string Recordtime, string Logtype, string Hospital)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 维护记录表 set 任务Flag='" + RP_Flag + "',图片数量='"
                + Photocount + "',音频数量='" + Voicecount + "',记录内容='" + Content + "',记录人='" + Recordperson
                + "',记录时间='" + Recordtime + "',记录类型='" + Logtype + "', 所属医院='" + Hospital + "' where Flag='" + Flag + "'AND convert(nvarchar(255),所属医院)= '" + Hospital + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();

            return result;


        }

        public string Update_EQ_Info(string flag, string EQNr, string name, string SrNr, string Type, string Factory, string Distributor, string Prise, string BuyDate, string Department, string Hospital, string EQType, string AssetsType, string FctEngineerID, string HspEngineerID, string Admin, string Service_TEL, string Remarks)
        {
            if (EQNr.Equals(""))
                EQNr = "无";
            if (name.Equals(""))
                name = "无";           //设备名称

            if (SrNr.Equals(""))
                SrNr = "无";
            if (Type.Equals(""))
                Type = "无";
            if (Factory.Equals(""))
                Factory = "无";
            if (Distributor.Equals(""))
                Distributor = "无";

            if (Prise.Equals(""))

                Prise = "0";
            if (BuyDate.Equals(""))
                BuyDate = "无";
            if (Department.Equals(""))
                Department = "无";
            if (Hospital.Equals(""))
                Hospital = "无";
            if (EQType.Equals(""))

                EQType = "无";
            if (AssetsType.Equals(""))
                AssetsType = "无";
            if (FctEngineerID.Equals(""))

                FctEngineerID = "无";
            if (HspEngineerID.Equals(""))
                HspEngineerID = "无";
            if (Admin.Equals(""))
                Admin = "无";
            if (Service_TEL.Equals(""))
                Service_TEL = "无";
            if (Remarks.Equals(""))
                Remarks = "无";
            DataBase dbs = new DataBase();
            string selectStr = "update 设备表 set 设备编号='" + EQNr + "',设备名称='"
                + name + "',出厂编号='" + SrNr + "',设备型号='" + Type + "',生产厂商='" + Factory
                + "',经销商='" + Department + "',购入价格='" + Prise + "', 购入日期='" + BuyDate + "', 所属科室='" + Department + "',所属医院='" + Hospital + "',设备类型='" + EQType
                + "',资产类型='" + AssetsType + "',厂商工程师='" + FctEngineerID + "',医院工程师='"
                + HspEngineerID + "',设备负责人='" + Admin + "',售后电话='" + Service_TEL + "',备注='" + Remarks + "'where Flag='" + flag + "'AND convert(nvarchar(255),所属医院)= '" + Hospital + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();

            return result;

        }



        public string Update_PushID(string UserID, string PushID, string ChannelID, string Hospital)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 用户表 set PushID='" + PushID + "',ChannelID='" + ChannelID + "'where 用户ID='" + UserID + "'AND convert(nvarchar(255),所属医院)= '" + Hospital + "'";
            dbs.RunProc(selectStr);
            dbs.Dispose();

            return "ok";

        }


        public string Update_Token(string UserName, string token)
        {
            DataBase dbs = new DataBase();
        
            string selectStr = "update Users set Token='" + token + "'where Name='" + UserName + "'";
            dbs.RunProc(selectStr);
            dbs.Dispose();

            return "ok";

        }


        public string Get_Hospital_By_Passcode(string passcode)
        {
            DataBase db = new DataBase();


            DataSet ds = new DataSet();

            string selectStr = "select 所属医院 FROM 用户表 WHERE convert(nvarchar(255),Token)= '" + passcode + "'";

            ds = db.RunProcReturnDS(selectStr);

            if (ds.Tables[0].Rows.Count > 0)

                return ds.Tables[0].Rows[0][0].ToString().Trim();
            else
                return "0";

            
        }

        public string Command(string command)
        {
            DataBase db = new DataBase();

          
           

            
        
            
            int result = db.RunProc(command);

           
                return result.ToString();


        }

        public DataSet Command2(string command)
        {
            DataBase db = new DataBase();







            return db.RunProcReturnDS(command);




        }

/// <summary>
/// EQList
/// </summary>
/// <param name="Department"></param>
/// <param name="Hospital"></param>
/// <returns></returns>

        public DataSet EQ_List_By_Department(string DepartmentID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID, Flag, 设备编号,设备名称,出厂编号, 设备型号, 生产厂商,所属医院 from 设备表  where convert(nvarchar(255),所属科室)= '" + DepartmentID + "'AND convert(nvarchar(255),设备表.删除标识)= '0'";

          

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet EQ_List_By_FirstDept(string DepartmentID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            int min =  Int32.Parse(DepartmentID);
            int max = Int32.Parse(DepartmentID.Trim()) + 9999;


            string selectStr = "select  设备表.Flag as 资产编号,设备表.设备名称,设备表.出厂编号, 设备表.设备型号, 设备表.生产厂商,Depts.Name as 所属部门  from 设备表 Join Depts On 设备表.所属科室=Depts.DeptNumber Join 设备PM计划表 ON 设备表.Flag= 设备PM计划表.设备Flag  where 所属科室>" + min + " and 所属科室 <" + max + " and 设备PM计划表.下次维护日期='20500101'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Serv_EQ_List_By_Department(string DepartmentID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  Flag as 资产编号,设备名称, 设备型号,购入价格 as 设备原值,出厂编号, 生产厂商,经销商 from 设备表  where convert(nvarchar(255),所属科室)= '" + DepartmentID + "'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


     




        public DataSet EQ_List_By_Department_Pages(string DepartmentID, string count)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            //string selectStr = "select ID, Flag, 设备编号,设备名称,出厂编号, 设备型号, 生产厂商,所属医院 from 设备表  where convert(nvarchar(255),所属科室)= '" + DepartmentID + "'AND convert(nvarchar(255),设备表.删除标识)= '0'";

            string selectStr = "select  TOP 20 ID, Flag, 设备编号,设备名称,出厂编号, 设备型号, 生产厂商,购入日期,所属医院 FROM  设备表  WHERE convert(nvarchar(255),所属科室)= '" + DepartmentID + "' AND convert(nvarchar(255),设备表.删除标识)= '0'  AND ID NOT IN (SELECT TOP " + count + " ID FROM 设备表 WHERE convert(nvarchar(255),所属科室)= '" + DepartmentID + "'AND convert(nvarchar(255),设备表.删除标识)= '0' ORDER BY 设备表.ID desc) order by 设备表.ID desc";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


        public DataSet EQ_List_By_Department_Check(string DepartmentID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID, Flag, 设备编号,设备名称,出厂编号, 设备型号, 生产厂商,盘点标识,备注 from 设备表  where convert(nvarchar(255),所属科室)= '" + DepartmentID + "'and convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;

        }
        public DataSet EQ_List_By_Department_Checked(string DepartmentID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID, Flag, 设备编号,设备名称,出厂编号, 设备型号, 生产厂商,所属医院 from 设备表  where convert(nvarchar(255),所属科室)= '" + DepartmentID + "' AND convert(nvarchar(255),设备表.盘点标识)!= '等待盘点' AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;

        }

        public DataSet EQ_List_By_Department_Overage(string DepartmentID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID, Flag, 设备编号,设备名称,出厂编号, 设备型号, 生产厂商,所属医院 from 设备表  where convert(nvarchar(255),所属科室)= '" + DepartmentID + "' AND convert(nvarchar(255),设备表.盘点标识)!= '盘盈' AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;

        }



        public DataSet EQ_List_By_Department_Shortage(string DepartmentID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID, Flag, 设备编号,设备名称,出厂编号, 设备型号, 生产厂商,所属医院 from 设备表  where convert(nvarchar(255),所属科室)= '" + DepartmentID + "' AND convert(nvarchar(255),设备表.盘点标识)= '盘亏' AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;

        }

        public DataSet EQ_List_For_PM(string DeptID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID, Flag, 出厂编号,设备名称,设备型号,下次PM日期,PM周期 from 设备表  where convert(nvarchar(255),所属科室)= '" + DeptID + "'AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }






        public DataSet Find_EQ_Find_List(string FindKey)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select  设备表.Flag as 资产编号,设备表.设备名称,设备表.购入价格,设备表.设备型号, 设备表.生产厂商,设备表.出厂编号,Depts.Name as 使用科室 from 设备表 JOIN Depts ON  convert(nvarchar(255),设备表.所属科室)= convert(nvarchar(255),Depts.DeptNumber)  where  convert(nvarchar(255),设备表.设备名称)LIKE '%'+'" + FindKey + "'+'%' AND convert(nvarchar(255),设备表.删除标识)= '0'";


            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public DataSet Find_EQ_Find_Dept(string FindKey, string DeptNr)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select  设备表.Flag as 资产编号,设备表.设备名称,设备表.购入价格,设备表.设备型号, 设备表.生产厂商, 设备表.出厂编号,Depts.Name as 使用科室 from 设备表 JOIN Depts ON  convert(nvarchar(255),设备表.所属科室)= convert(nvarchar(255),Depts.DeptNumber)  where  convert(nvarchar(255),设备表.设备名称)LIKE '%'+'" + FindKey + "'+'%' And convert(nvarchar(255),设备表.所属科室)LIKE '%'+'" + DeptNr + "'+'%' AND convert(nvarchar(255),设备表.删除标识)= '0'";


            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Find_EQ_By_Sr(string Sr)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select  设备表.Flag as 资产编号,设备表.设备名称,设备表.购入价格,设备表.设备型号, 设备表.生产厂商, 设备表.出厂编号,Depts.Name as 使用科室 from 设备表 JOIN Depts ON  convert(nvarchar(255),设备表.所属科室)= convert(nvarchar(255),Depts.DeptNumber)  where  convert(nvarchar(255),设备表.出厂编号)LIKE '%'+'" + Sr + "'+'%' ";


            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public DataSet HSP_Department_PM_List_Win(string HospitalID, string Month)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select Name,DeptNumber FROM Depts   WHERE  convert(nvarchar(255),PID)= '" + HospitalID + "'and convert(nvarchar(255),PM)= '" + Month + "'";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public DataSet HSP_Department_Un_PM_List_Win(string HospitalID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select Name,DeptNumber FROM Depts   WHERE  convert(nvarchar(255),PID)= '" + HospitalID + "'and convert(nvarchar(255),PM)= '0'";
            ds = db.RunProcReturnDS(selectStr);
            return ds;

           
        }
        public DataSet Find_EQ_Find_List_Dept(string DeptID, string FindKey)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID, Flag, 设备编号,设备名称,出厂编号, 设备型号, 生产厂商,所属医院 from 设备表  where  convert(nvarchar(255),设备名称)LIKE '%'+'" + FindKey + "'+'%' AND convert(nvarchar(255),所属科室)= '" + DeptID + "'";


            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }









        public DataSet Mes_EQ_List_By_Department(string Department)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID, Flag,设备名称,出厂编号,设备型号 from 设备表 where convert(nvarchar(255),所属科室)= '" + Department + "' and convert(nvarchar(255),计量标识)='是'AND convert(nvarchar(255),删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Get_Measurement_List(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select* from 计量检定表 where convert(nvarchar(255),EQFlag)= '" + EQFlag + "' ";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


        public DataSet Message_List()
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID,标题,内容,发送人,发送时间 from 通知表  order by ID desc";

            ds = db.RunProcReturnDS(selectStr);

            return ds;


        }

        public DataSet Message_List_Top_5()
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select Top 5 * from 通知表  order by ID desc";

            ds = db.RunProcReturnDS(selectStr);

            return ds;


        }


        public string Add_Message(string Title, string Content, string AddUser)
        {
            DataBase db = new DataBase();


            int result = db.RunProc("insert into 通知表 (标题,内容,发送人,发送时间) values('" + Title + "','" + Content + "','" + AddUser + "','" + DateTime.Now.ToShortDateString() + "')");

            return result.ToString();

        }

        public string Del_Message(string ID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "delete from 通知表  where ID='"+ID+"'";

            return db.RunProc(selectStr).ToString();

           


        }









        public DataSet EQ_List_By_EngineerID(string EngineerID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select 设备表.ID, 设备表.Flag, 设备表.设备编号, 设备表.设备名称,设备表.出厂编号, 设备表.设备型号, 设备表.生产厂商, 设备表.经销商, 设备表.购入价格, 设备表.购入日期,设备表.所属科室, 设备表.所属医院,设备表.设备类型, 设备表.资产类型, 设备表.厂商工程师, 设备表.医院工程师, 设备表.设备负责人, 设备表.售后电话, 设备表.备注, 设备表.图片数量,设备表.存放位置, 设备表.计量标识 ,设备表.下次PM日期,设备表.PM周期,设备表.证书数量,设备表.机房ID,设备表.管理科室,Users.ChineseName,Users.CellPhone,Users.Name from 设备表  JOIN Users ON  convert(nvarchar(255),设备表.医院工程师)= convert(nvarchar(255),Users.ID) where convert(nvarchar(255),设备表.医院工程师)= '" + EngineerID + "' AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }



        public DataSet EQ_List_By_ServiceID(string ServiceID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select 设备表.ID,设备表.Flag, 设备表.设备名称,设备表.出厂编号,设备表.设备型号,Depts.Name from 设备表 JOIN Depts ON  convert(nvarchar(255),设备表.所属医院)= convert(nvarchar(255),Depts.DeptNumber) where convert(nvarchar(255),设备表.厂商工程师)= '" + ServiceID + "' AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }









        public string Update_Task_Spare(string flag,string sparecount,string sparecost)




        {





            DataBase dbs = new DataBase();
            string selectStr = "update 任务表 set 配件数量='" + sparecount + "',配件费='" + sparecost + "'  where convert(nvarchar(255),Flag)='" + flag + "'  ";
            int result = dbs.RunProc(selectStr);
            dbs.Dispose();
            return result.ToString();
        
        
        
        
        }






        public string Update_Depts_PM_Win(string DeptNumber, string Month)
        {





            DataBase dbs = new DataBase();
            string selectStr = "update Depts set PM='" + Month + "'  where convert(nvarchar(255),DeptNumber)='" + DeptNumber + "' or convert(nvarchar(255),PID)='" + DeptNumber + "'  ";
            int result = dbs.RunProc(selectStr);
            int result2=0;



            string ss = "select DeptNumber from Depts where   convert(nvarchar(255),PID)='" + DeptNumber + "'  ";
            DataSet ds = dbs.RunProcReturnDS(ss);

            for(int i=0;i<ds.Tables[0].Rows.Count;i++)
            {
                selectStr = "update Depts set PM='" + Month + "'  where  convert(nvarchar(255),PID)='" + ds.Tables[0].Rows[i][0].ToString() + "'  ";
                result2=  dbs.RunProc(selectStr);
            
            


            }



            dbs.Dispose();



          /// Update_EQPM_Plan(DeptNumber, Month);
            return (result + result2).ToString();


        }




       


        public string Update_EQPM_Plan(string DeptNumber, string Month)
        {
       
            int EQCout=0;
            DataBase dbs = new DataBase();
            string ss = "select DeptNumber from Depts where   convert(nvarchar(255),PID)='" + DeptNumber + "'  ";
            DataSet ds = dbs.RunProcReturnDS(ss);




            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string selectStr = "select Flag from 设备表 where  convert(nvarchar(255),所属科室)='" + ds.Tables[0].Rows[i][0].ToString() + "'  ";
                DataSet ds2 = dbs.RunProcReturnDS(selectStr);

                for (int j = 0; j < ds2.Tables[0].Rows.Count; j++)
                {
                    string year=DateTime.Today.Year.ToString();
                    if (Int16.Parse(Month.ToString()) < Int16.Parse(DateTime.Today.Month.ToString()))
                        year = (DateTime.Today.Year + 1).ToString();
                    if (Month.Length<2)
                        Month="0"+Month;
                   
                    string nextdate=year+Month+"15";
                     if(Month.Equals("0"))
                        nextdate="20201215";

                    string selectStr3 = "update 设备PM计划表 set 下次维护日期='" + nextdate + "'  where  convert(nvarchar(255),设备Flag)='" + ds2.Tables[0].Rows[j][0].ToString() + "'"; 
                   dbs.RunProcReturnDS(selectStr3);
                   EQCout = j;


                }
            }


            return EQCout.ToString();

        }

















        public string Get_HSP_Engineer_Phone_By_EQID(string EQID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 设备表.资产编号,设备表.医院工程师,用户表.电话 from 设备表  JOIN 用户表 ON  convert(nvarchar(255),设备表.医院工程师)=convert(nvarchar(255),用户表.用户ID) where  convert(nvarchar(255),设备表.资产编号)='" + EQID + "' AND convert(nvarchar(255),设备表.删除标识)= '0'";

            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)

                return ds.Tables[0].Rows[0][2].ToString().Trim();
            else
                return "0";


        }
     

     


        public int Check_Token(string token)
        {
            DataBase db = new DataBase();
            DataSet ds = new DataSet();

            string selectStr = "select * from 验证表 where convert(nvarchar(255),token)='" + token + "'";

            ds = db.RunProcReturnDS(selectStr);



            return ds.Tables[0].Rows.Count;


        }





        public DataSet EQCalllistByDepartment(string Department)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  ID,资产编号,设备名称,设备型号,出厂编号,所属科室,厂商工程师,医院工程师,图片标识,备注 from 设备表 where convert(nvarchar(255),所属科室)= '" + Department + "' AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet EQlistByEQType(string eqtype)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  ID,资产编号,设备名称,设备型号,出厂编号,设备类别,所属科室,厂商工程师,医院工程师,图片标识,备注 from 设备表 where convert(nvarchar(255),设备类别)= '" + eqtype + "' AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet EQlistByName(string name, string department)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID,资产编号,设备名称,设备型号,出厂编号,设备类别,生产厂家,购买日期,厂商工程师,医院工程师,图片标识,备注,所属医院 from 设备表 where convert(nvarchar(255),设备名称)LIKE '%'+'" + name + "'+'%'AND convert(nvarchar(255),所属科室)= '" + department + "' AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }




        public DataSet EQ_List_Engineer_HSP(string Engineer_HSP)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID,资产编号,设备名称,设备型号,出厂编号,设备类别,生产厂家,购买日期,厂商工程师,医院工程师,图片标识,备注,所属医院 from 设备表 where convert(nvarchar(255),医院工程师)='" + Engineer_HSP + "' AND convert(nvarchar(255),设备表.删除标识)= '0'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }




        public string Update_EQ_Image_Count(string flag, string imagecount, string Hospital)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 设备表 set 图片数量='" + imagecount + "' where convert(nvarchar(255),Flag)='" + flag + "' AND convert(nvarchar(255),所属医院)='" + Hospital + "' ";
            int result = dbs.RunProc(selectStr);
            dbs.Dispose();
            return result.ToString();

        }




        public string Update_PM_Spare_Count(string flag, string sparecount, string Hospital)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 任务表 set 配件数量='" + sparecount + "' where convert(nvarchar(255),Flag)='" + flag + "' AND convert(nvarchar(255),所属医院)='" + Hospital + "' ";
            int result = dbs.RunProc(selectStr);
            dbs.Dispose();
            return result.ToString();

        }



        public string Insert_Task_Cost(string TaskFlag, string SpareCost, string RepairCost)
        {
            DataBase db = new DataBase();


            int result = db.RunProc("insert into 费用表 (任务Flag,配件费,维修费) values('" + TaskFlag + "','" + SpareCost + "','" + RepairCost + "')");

            return result.ToString();

        }

        public string Serv_Add_Dept(string DeptName, string HSPID)
        {
            DataBase db = new DataBase();

            
            int result = db.RunProc("insert into Depts (Name,ParentID) values('" + DeptName + "','" + HSPID + "')");

            return result.ToString();

        }
        public string Add_Dept_WinClient(string DeptName,string DeptNr,string ParentID,string DeptPor)
        {
            DataBase db = new DataBase();


            int result = db.RunProc("insert into Depts (Name,PID,DeptNumber,DeptPor,ParentID) values('" + DeptName + "','" +ParentID+ "','" + DeptNr + "','" + DeptPor + "','1')");

            return result.ToString();

        }







      


        public DataSet RP_list_UnFinish(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select* FROM 任务表 WHERE convert(nvarchar(255),设备Flag)= '" + EQFlag + "'AND convert(nvarchar(255),维修进程)!= '完成' AND convert(nvarchar(255),任务类别)!= 'PM'  order by ID desc";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
      


        public DataSet RPlist_UnFinish_By_Department(string Department)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 任务表.ID,任务表.Flag,设备表.Flag,设备表.设备名称,任务表.报修时间,任务表.维修进程,设备表.存放位置 FROM 设备表 JOIN 任务表  ON  convert(nvarchar(255),任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) WHERE convert(nvarchar(255),设备表.所属科室)= '" + Department + "'AND convert(nvarchar(255),任务表.维修进程)!= '任务完成' AND convert(nvarchar(255),任务表.任务类别)!= 'PM'  order by 任务表.ID desc";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet RPlist_Mec(string UserID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)  WHERE convert(nvarchar(255),报修任务表.报修人)= '" + UserID + "' AND convert(nvarchar(255),维修进程)!= '任务完成'  order by ID desc";
            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][5].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][5] = "0";
                        ds.Tables[0].Rows[i][6] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][5].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][5] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[5].ColumnName = "UserName";
                ds.Tables[0].Columns[6].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
       

        public DataSet RPlist_Admin(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)    WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "'AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' order by 报修任务表.ID desc";
            
            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_Admin_Win(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,报修任务表.报修人,报修任务表.报修人 FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber)     WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "'AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_All(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.报修人,Users.Name,Users.ChineseName,报修任务表.执行人,Users2.Name,Users2.ChineseName,Depts.Name,设备表.设备名称,报修任务表.审批意见,报修任务表.审批附言,Users3.ChineseName,报修任务表.市场价格,报修任务表.维修预算,报修任务表.预算附言 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID) LEFT  JOIN  Users as Users2  ON  convert(nvarchar(255),Users2.ID)=convert(nvarchar(255),报修任务表.执行人) LEFT  JOIN  Users as Users3  ON  convert(nvarchar(255),Users3.ID)=convert(nvarchar(255),报修任务表.审批人)     WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);



   
            





            return ds;


        }
        public DataSet RPlist_All_Win(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.报修人,报修任务表.报修人,报修任务表.报修人,报修任务表.执行人,Users2.Name,Users2.ChineseName,Depts.Name,设备表.设备名称,报修任务表.审批意见,报修任务表.审批附言,Users3.ChineseName,报修任务表.市场价格,报修任务表.维修预算,报修任务表.预算附言 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber)  LEFT  JOIN  Users as Users2  ON  convert(nvarchar(255),Users2.ID)=convert(nvarchar(255),报修任务表.执行人) LEFT  JOIN  Users as Users3  ON  convert(nvarchar(255),Users3.ID)=convert(nvarchar(255),报修任务表.审批人)     WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);










            return ds;


        }
        public DataSet RPlist_Unfinish_Win(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.报修人,报修任务表.报修人,报修任务表.报修人,报修任务表.执行人,Users2.Name,Users2.ChineseName,Depts.Name,设备表.设备名称,报修任务表.审批意见,报修任务表.审批附言,Users3.ChineseName,报修任务表.市场价格,报修任务表.维修预算,报修任务表.预算附言,dp.Name,Depts.DeptNumber FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Depts as dp  ON  convert(nvarchar(255),报修任务表.所属医院)=convert(nvarchar(255),dp.DeptNumber)  LEFT  JOIN  Users as Users2  ON  convert(nvarchar(255),Users2.ID)=convert(nvarchar(255),报修任务表.执行人) LEFT  JOIN  Users as Users3  ON  convert(nvarchar(255),Users3.ID)=convert(nvarchar(255),报修任务表.审批人)     WHERE convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string selectStr2 = "select dp.Name from Depts join Depts as dp on convert(nvarchar(255),Depts.PID)=convert(nvarchar(255),dp.DeptNumber) where convert(nvarchar(255),Depts.DeptNumber)= '" + ds.Tables[0].Rows[i][21].ToString().Trim() + "'";
                    dstemp = db2.RunProcReturnDS(selectStr2);
                    if (dstemp.Tables[0].Rows.Count > 0)
                    {
                        ds.Tables[0].Rows[i][20] = dstemp.Tables[0].Rows[0][0].ToString();
                    }
                    else
                        ds.Tables[0].Rows[i][20] = "111";

                }



                ds.Tables[0].Columns[20].ColumnName = "所属学院";

                ds.AcceptChanges();



            }











            return ds;


        }
        public DataSet RPlist_Unapprove_Win(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.报修人,报修任务表.报修人,报修任务表.报修人,报修任务表.执行人,Users2.Name,Users2.ChineseName,Depts.Name,设备表.设备名称,报修任务表.审批意见,报修任务表.审批附言,Users3.ChineseName,报修任务表.市场价格,报修任务表.维修预算,报修任务表.预算附言 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) LEFT  JOIN  Users as Users2  ON  convert(nvarchar(255),Users2.ID)=convert(nvarchar(255),报修任务表.申请人) LEFT  JOIN  Users as Users3  ON  convert(nvarchar(255),Users3.ID)=convert(nvarchar(255),报修任务表.审批人)     WHERE  (convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' and convert(nvarchar(255),报修任务表.审批意见)!= '无需审批') order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);

            return ds;


        }

        public DataSet RPlist_Unapprove_Chief(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.报修人,Users.Name,Users.ChineseName,报修任务表.执行人,Users2.Name,Users2.ChineseName,Depts.Name,设备表.设备名称,报修任务表.审批意见,报修任务表.审批附言,Users3.ChineseName,报修任务表.市场价格,报修任务表.维修预算,报修任务表.预算附言 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID) LEFT  JOIN  Users as Users2  ON  convert(nvarchar(255),Users2.ID)=convert(nvarchar(255),报修任务表.申请人) LEFT  JOIN  Users as Users3  ON  convert(nvarchar(255),Users3.ID)=convert(nvarchar(255),报修任务表.审批人)     WHERE convert(nvarchar(255),报修任务表.审批意见)= '等待学校审批' or  convert(nvarchar(255),报修任务表.审批意见)= '等待处长审批' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);

            return ds;


        }
        public DataSet RPlist_Unapprove_EGN_iOS(string EGNID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.报修人,Users.Name,Users.ChineseName,报修任务表.执行人,Users2.Name,Users2.ChineseName,Depts.Name,设备表.设备名称,报修任务表.审批意见,报修任务表.审批附言,Users3.ChineseName,报修任务表.市场价格,报修任务表.维修预算,报修任务表.预算附言 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID) LEFT  JOIN  Users as Users2  ON  convert(nvarchar(255),Users2.ID)=convert(nvarchar(255),报修任务表.申请人) LEFT  JOIN  Users as Users3  ON  convert(nvarchar(255),Users3.ID)=convert(nvarchar(255),报修任务表.审批人)     WHERE convert(nvarchar(255),报修任务表.申请人)= '" + EGNID + "' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);










            return ds;



        }

        public DataSet User_List_By_Dept(string DeptID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();





            string selectStr = "select ID,ChineseName FROM Users WHERE convert(nvarchar(255),Users.DeptID)= '" + DeptID + "' ";

            ds = db.RunProcReturnDS(selectStr);




            return ds;



        }
        public DataSet Dept_List_By_User(string UserID)
        {
            DataBase db = new DataBase();
            
            DataSet ds = new DataSet();





            string selectStr = "select Depts.DeptNumber,Depts.Name FROM 负责科室表  JOIN Depts  ON  convert(nvarchar(255),负责科室表.DeptID)=convert(nvarchar(255),Depts.DeptNumber) WHERE convert(nvarchar(255),负责科室表.UserID)= '" + UserID + "' ";

            ds = db.RunProcReturnDS(selectStr);




            return ds;



        }
        public DataSet RPlist_approved_Win(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.报修人,Users.Name,Users.ChineseName,报修任务表.执行人,Users2.Name,Users2.ChineseName,Depts.Name,设备表.设备名称,报修任务表.审批意见,报修任务表.审批附言,Users3.ChineseName,报修任务表.市场价格,报修任务表.维修预算,报修任务表.预算附言 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID) LEFT  JOIN  Users as Users2  ON  convert(nvarchar(255),Users2.ID)=convert(nvarchar(255),报修任务表.申请人) LEFT  JOIN  Users as Users3  ON  convert(nvarchar(255),Users3.ID)=convert(nvarchar(255),报修任务表.审批人)     WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "'and (convert(nvarchar(255),报修任务表.审批意见)= '同意') order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);










            return ds;


        }

        public DataSet Task_Approved_List(string TaskFlag)
        {
            DataBase db = new DataBase();
  
            DataSet ds = new DataSet();
            




            string selectStr = "select 审批表.ID,审批表.审批意见,审批表.审批附言,审批表.审批时间,Users.ChineseName FROM  审批表 JOIN Users  ON  convert(nvarchar(255),审批表.审批人)=convert(nvarchar(255),Users.ID)   WHERE convert(nvarchar(255),审批表.任务Flag)= '" + TaskFlag + "'  order by 审批表.ID desc";

            ds = db.RunProcReturnDS(selectStr);


            return ds;


        }
        public DataSet EQ_RP_List_Win(string EQFlag)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.报修人,Users.Name,Users.ChineseName,报修任务表.执行人,Users2.Name,Users2.ChineseName,Depts.Name,设备表.设备名称,报修任务表.审批意见,报修任务表.审批附言,Users3.ChineseName,报修任务表.市场价格,报修任务表.维修预算,报修任务表.预算附言 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID) LEFT  JOIN  Users as Users2  ON  convert(nvarchar(255),Users2.ID)=convert(nvarchar(255),报修任务表.执行人) LEFT  JOIN  Users as Users3  ON  convert(nvarchar(255),Users3.ID)=convert(nvarchar(255),报修任务表.审批人)     WHERE convert(nvarchar(255),报修任务表.设备Flag)= '" + EQFlag + "'and convert(nvarchar(255),报修任务表.审批意见)!= '同意' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);










            return ds;


        }

        public DataSet RPlist_Top5(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select Top 5 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,报修任务表.报修人,报修任务表.报修人,设备表.设备名称 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber)   WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_UnProv(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName,设备表.设备名称,报修任务表.审批意见 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)    WHERE convert(nvarchar(255),报修任务表.审批意见)!= '同意' and convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_UnProv_Top5(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select Top 5 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName,设备表.设备名称,报修任务表.审批意见 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)    WHERE convert(nvarchar(255),报修任务表.审批意见)!= '同意' and convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }


        public DataSet RPlist_Vote_Top5(string HSPID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select Top 5 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName,设备表.设备名称,报修任务表.服务评价 FROM  报修任务表  JOIN 设备表  ON  convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)    WHERE convert(nvarchar(255),报修任务表.服务评价)!= '无' and convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' order by 报修任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }

        public DataSet EQ_Record_list(string EQFlag)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();




            string selectStr = "select 任务表.ID,任务表.Flag,任务表.故障描述,任务表.报修时间,任务表.维修进程,任务表.执行人,任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  任务表  JOIN Depts  ON  convert(nvarchar(255),任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),任务表.报修人)=convert(nvarchar(255),Users.ID)    WHERE convert(nvarchar(255),任务表.设备Flag)= '" + EQFlag + "' order by 任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][5].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][5] = "0";
                        ds.Tables[0].Rows[i][6] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][5].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][5] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[5].ColumnName = "UserName";
                ds.Tables[0].Columns[6].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_EGN_iOS(string UserID,string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            //string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE (convert(nvarchar(255),报修任务表.执行人)= '" + UserID + "' OR convert(nvarchar(255),报修任务表.报修人)='" + UserID + "') AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' AND 报修任务表.ID NOT IN(SELECT  报修任务表.ID FROM 报修任务表 ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";

            string selectStr = "select  TOP 20 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE (convert(nvarchar(255),报修任务表.执行人)= '" + UserID + "' OR convert(nvarchar(255),报修任务表.报修人)='" + UserID + "')  AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE (convert(nvarchar(255),报修任务表.执行人)= '" + UserID + "' OR convert(nvarchar(255),报修任务表.报修人)='" + UserID + "') ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";
            
            
            
            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet EQ_Task_iOS(string EQFlag, string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            //string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE (convert(nvarchar(255),报修任务表.执行人)= '" + UserID + "' OR convert(nvarchar(255),报修任务表.报修人)='" + UserID + "') AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' AND 报修任务表.ID NOT IN(SELECT  报修任务表.ID FROM 报修任务表 ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";

            string selectStr = "select  TOP 20 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE convert(nvarchar(255),报修任务表.设备Flag)='" + EQFlag + "' AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE  convert(nvarchar(255),报修任务表.设备Flag)='" + EQFlag + "' ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";
            
            
            
            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_Tech_iOS(string UserID, string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            //string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE (convert(nvarchar(255),报修任务表.执行人)= '" + UserID + "' OR convert(nvarchar(255),报修任务表.报修人)='" + UserID + "') AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' AND 报修任务表.ID NOT IN(SELECT  报修任务表.ID FROM 报修任务表 ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";

            string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName,Depts.DeptNumber FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE convert(nvarchar(255),报修任务表.报修人)= '" + UserID + "' AND convert(nvarchar(255),报修任务表.维修进程)!='任务完成'  AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE  convert(nvarchar(255),报修任务表.报修人)= '" + UserID + "' AND convert(nvarchar(255),报修任务表.维修进程)!='任务完成' ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";



            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string selectstr3 = "select D3.Name from Depts join Depts as D3 on convert(nvarchar(255),D3.DeptNumber)=convert(nvarchar(255),Depts.PID) where convert(nvarchar(255),Depts.DeptNumber)= '" + ds.Tables[0].Rows[i][11].ToString().Trim() + "'";

                    dstemp = db2.RunProcReturnDS(selectstr3);

                    ds.Tables[0].Rows[i][11] = dstemp.Tables[0].Rows[0][0].ToString();
                    ds.Tables[0].Rows[i][8] = dstemp.Tables[0].Rows[0][0].ToString() + " " + ds.Tables[0].Rows[i][8];
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_History_Tech(string UserID, string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            //string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE (convert(nvarchar(255),报修任务表.执行人)= '" + UserID + "' OR convert(nvarchar(255),报修任务表.报修人)='" + UserID + "') AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' AND 报修任务表.ID NOT IN(SELECT  报修任务表.ID FROM 报修任务表 ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";

            string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName,Depts.DeptNumber FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE convert(nvarchar(255),报修任务表.报修人)= '" + UserID + "' AND convert(nvarchar(255),报修任务表.维修进程)='任务完成'  AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE  convert(nvarchar(255),报修任务表.报修人)= '" + UserID + "' AND convert(nvarchar(255),报修任务表.维修进程)='任务完成' ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";



            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string selectstr3 = "select D3.Name from Depts join Depts as D3 on convert(nvarchar(255),D3.DeptNumber)=convert(nvarchar(255),Depts.PID) where convert(nvarchar(255),Depts.DeptNumber)= '" + ds.Tables[0].Rows[i][11].ToString().Trim() + "'";

                    dstemp = db2.RunProcReturnDS(selectstr3);

                    ds.Tables[0].Rows[i][11] = dstemp.Tables[0].Rows[0][0].ToString();
                    ds.Tables[0].Rows[i][8] = dstemp.Tables[0].Rows[0][0].ToString() + " " + ds.Tables[0].Rows[i][8];
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_Admin_iOS(string HSPID, string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName ,Depts.DeptNumber  FROM 报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "'  AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "'  ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";



            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {


                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string selectstr3 = "select D3.Name from Depts join Depts as D3 on convert(nvarchar(255),D3.DeptNumber)=convert(nvarchar(255),Depts.PID) where convert(nvarchar(255),Depts.DeptNumber)= '" + ds.Tables[0].Rows[i][11].ToString().Trim() + "'";

                    dstemp = db2.RunProcReturnDS(selectstr3);

                    ds.Tables[0].Rows[i][11] = dstemp.Tables[0].Rows[0][0].ToString();
                    ds.Tables[0].Rows[i][8] = dstemp.Tables[0].Rows[0][0].ToString() + " " + ds.Tables[0].Rows[i][8];

                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_Admin_Unfinish(string HSPID, string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();

        
            string selectStr = "select  TOP 20 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName,Depts.DeptNumber,设备表.设备名称 FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID) join 设备表 on convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成 ' AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成 '  ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";



            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string selectstr3 = "select D3.Name from Depts join Depts as D3 on convert(nvarchar(255),D3.DeptNumber)=convert(nvarchar(255),Depts.PID) where convert(nvarchar(255),Depts.DeptNumber)= '" + ds.Tables[0].Rows[i][11].ToString().Trim() + "'";

                    dstemp = db2.RunProcReturnDS(selectstr3);

                    ds.Tables[0].Rows[i][11] = dstemp.Tables[0].Rows[0][0].ToString();
                    ds.Tables[0].Rows[i][8] = dstemp.Tables[0].Rows[0][0].ToString()+" "+ds.Tables[0].Rows[i][8];


                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                      
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }
                ds.Tables[0].Columns[11].ColumnName = "上级部门";
                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_All_iOS(string HSPID, string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName,Depts.DeptNumber,设备表.设备名称  FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)  join 设备表 on convert(nvarchar(255),报修任务表.设备Flag)=convert(nvarchar(255),设备表.Flag)  WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "'  AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "'   ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";



            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                     string selectstr3 = "select D3.Name from Depts join Depts as D3 on convert(nvarchar(255),D3.DeptNumber)=convert(nvarchar(255),Depts.PID) where convert(nvarchar(255),Depts.DeptNumber)= '" + ds.Tables[0].Rows[i][11].ToString().Trim() + "'";

                    dstemp = db2.RunProcReturnDS(selectstr3);

                    ds.Tables[0].Rows[i][11] = dstemp.Tables[0].Rows[0][0].ToString();
                    ds.Tables[0].Rows[i][8] = dstemp.Tables[0].Rows[0][0].ToString() + " " + ds.Tables[0].Rows[i][8];
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }



            


            return ds;


        }
        public DataSet RPlist_Wait_Comment(string HSPID, string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' AND convert(nvarchar(255),报修任务表.维修进程)= '任务完成' AND convert(nvarchar(255),报修任务表.服务评价)= '无' AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' AND convert(nvarchar(255),报修任务表.维修进程)= '任务完成' AND convert(nvarchar(255),报修任务表.服务评价)= '无'  ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";



            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }


        public DataSet Comment_Finish_List_iOS(string HSPID, string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName,报修任务表.服务评价,报修任务表.评价附言 FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' AND convert(nvarchar(255),报修任务表.维修进程)= '任务完成' AND convert(nvarchar(255),报修任务表.服务评价)!= '无' AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE convert(nvarchar(255),报修任务表.所属医院)= '" + HSPID + "' AND convert(nvarchar(255),报修任务表.维修进程)= '任务完成' AND convert(nvarchar(255),报修任务表.服务评价)!= '无'  ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";



            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_Admin_UnEVA_iOS(string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE  convert(nvarchar(255),报修任务表.服务评价)= '无' AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE  convert(nvarchar(255),报修任务表.服务评价)= '无'  ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";



            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
        public DataSet RPlist_Dept_iOS(string DeptID, string count)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            //string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.ID) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE (convert(nvarchar(255),报修任务表.执行人)= '" + UserID + "' OR convert(nvarchar(255),报修任务表.报修人)='" + UserID + "') AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' AND 报修任务表.ID NOT IN(SELECT  报修任务表.ID FROM 报修任务表 ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";

            string selectStr = "select  TOP 10 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE convert(nvarchar(255),报修任务表.所属科室)= '" + DeptID + "'  AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' AND 报修任务表.ID NOT IN(SELECT TOP " + count + " 报修任务表.ID FROM 报修任务表 WHERE convert(nvarchar(255),报修任务表.所属科室)= '" + DeptID + "' AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成' ORDER BY 报修任务表.ID desc) order by 报修任务表.ID desc";
            

            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }





           
            return ds;


        }

       
        



        public DataSet RPlist_EGN(string UserID)
        {
            DataBase db = new DataBase();
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();
            DataSet dstemp = new DataSet();


            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.设备Flag,报修任务表.故障描述,报修任务表.报修时间,报修任务表.维修进程,报修任务表.执行人,报修任务表.执行人,Depts.Name,Users.Name,Users.ChineseName FROM  报修任务表  JOIN Depts  ON  convert(nvarchar(255),报修任务表.所属科室)=convert(nvarchar(255),Depts.DeptNumber) JOIN Users  ON  convert(nvarchar(255),报修任务表.报修人)=convert(nvarchar(255),Users.ID)   WHERE (convert(nvarchar(255),报修任务表.执行人)= '" + UserID + "' OR convert(nvarchar(255),报修任务表.报修人)='" + UserID + "') AND convert(nvarchar(255),报修任务表.维修进程)!= '任务完成'  order by 报修任务表.ID desc";
            ds = db.RunProcReturnDS(selectStr);



            if (ds.Tables[0].Rows.Count > 0)
            {

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][6].ToString().Trim() == "无")
                    {
                        ds.Tables[0].Rows[i][6] = "0";
                        ds.Tables[0].Rows[i][7] = "暂无";
                    }
                    else
                    {
                        string selectStr2 = "select Name,ChineseName from Users where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[i][6].ToString().Trim() + "'";
                        dstemp = db2.RunProcReturnDS(selectStr2);

                        ds.Tables[0].Rows[i][6] = dstemp.Tables[0].Rows[0][0].ToString();
                        ds.Tables[0].Rows[i][7] = dstemp.Tables[0].Rows[0][1].ToString();
                    }

                }

                ds.Tables[0].Columns[6].ColumnName = "UserName";
                ds.Tables[0].Columns[7].ColumnName = "UserName2";
                ds.AcceptChanges();



            }






            return ds;


        }
      


      


        public string Update_Sr(string Flag, string Sr)
        {
            DataBase db = new DataBase();
        
            DataSet ds = new DataSet();
         


            return db.RunProc("update 设备表 set 出厂编号 ='" + Sr + "'  where  Flag= '" + Flag+ "'").ToString();



        }





        public string Add_Apply(string Flag, string ApplyContent, string facprice, string applyprice, string UserID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();



            return db.RunProc("update 报修任务表 set 审批意见 ='等待审批',市场价格='" + facprice + "', 维修预算='" + applyprice + "',预算附言='" + ApplyContent + "', 申请人='" + UserID + "'  where  Flag= '" + Flag + "'").ToString();



        }



        public string Add_Approve(string Flag, string Approve, string Approvecontent, string ApproveUserID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            db.RunProc("insert into 审批表 (任务Flag,审批意见,审批附言,审批人,审批时间) values('" + Flag + "','" + Approve + "','" + Approvecontent + "','" + ApproveUserID + "','" + GetTimeNow() + "')").ToString();


          return db.RunProc("update 报修任务表 set 审批意见 ='" + Approve + "',审批附言='" + Approvecontent + "', 审批人='" + ApproveUserID + "' where  Flag= '" + Flag + "'").ToString();



        }
        public string Add_User_Dept(string UserID, string DeptID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            return db.RunProc("insert into 负责科室表 (UserID,DeptID) values('" + UserID + "','" + DeptID + "')").ToString();

            


        }


        public string Add_Comment(string Flag, string Comment, string CommentContent, string UserID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();



            return db.RunProc("update 报修任务表 set 服务评价 ='" + Comment + "',评价附言='" + CommentContent + "', 评价人='" + UserID + "' where  Flag= '" + Flag + "'").ToString();



        }


        public int Service_Spare_Count()
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID FROM 销售配件表  where convert(nvarchar(255),销售配件表.审核标识)= '1'";

            return db.RunProcReturnDtable(selectStr).Rows.Count;


        }

        public string Get_HSPID_By_Name(string HSPName)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID FROM Depts  where convert(nvarchar(255),Name)= '"+HSPName+"'";
              
              ds = db.RunProcReturnDS(selectStr);
            if( ds.Tables[0].Rows.Count>0)
            return ds.Tables[0].Rows[0][0].ToString();
            else
            return "noHSP";


        }

        public DataSet PM_List_UnFinish(string HospitalID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select PM任务表.ID,设备表.设备名称,PM任务表.EQFlag,PM任务表.模板ID,b1.Name,PM任务表.是否完成,b1.ChineseName FROM 设备表 JOIN PM任务表  ON  convert(nvarchar(255),PM任务表.EQFlag)=convert(nvarchar(255),设备表.Flag) LEFT JOIN Users as b1 ON convert(nvarchar(255),PM任务表.执行人ID)=convert(nvarchar(255),b1.ID)  WHERE  convert(nvarchar(255),PM任务表.是否完成)!= '完成' and  convert(nvarchar(255),设备表.所属医院)= '" + HospitalID + "' order by PM任务表.ID desc";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public DataSet PM_List_UnFinish_Win()
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 设备表.设备名称,设备表.设备型号,c1.Name as 所属部门,c1.PID as 上级部门,PM任务表.EQFlag as 资产编号,PM任务表.模板ID,PM任务表.是否完成,b1.ChineseName as 执行人,PM任务表.生成日期 FROM 设备表 JOIN PM任务表  ON  convert(nvarchar(255),PM任务表.EQFlag)=convert(nvarchar(255),设备表.Flag) LEFT JOIN Users as b1 ON convert(nvarchar(255),PM任务表.执行人ID)=convert(nvarchar(255),b1.ID) LEFT JOIN Depts as c1 ON convert(nvarchar(255),设备表.所属科室)=convert(nvarchar(255),c1.DeptNumber)  WHERE  convert(nvarchar(255),PM任务表.是否完成)!= '完成'  order by PM任务表.ID desc";
            ds = db.RunProcReturnDS(selectStr);


            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                ds.Tables[0].Rows[i][3] = Get_Dept_PName(ds.Tables[0].Rows[i][3].ToString());

            }





                return ds;


        }

        public string Get_Dept_PName(string PID)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select Name FROM Depts  WHERE  convert(nvarchar(255),Depts.DeptNumber)= '"+PID+"'  ";
            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows[0][0].ToString();
        
        
        
        
        }
            
            
            
            
            

        public DataSet HSP_Department_List(string HospitalID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,Name,DeptNumber FROM Depts   WHERE  convert(nvarchar(255),PID)= '" + HospitalID + "'";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


        public DataSet Department_List_By_PID(string PID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,Name,DeptNumber FROM Depts   WHERE  convert(nvarchar(255),PID)= '" + PID + "'";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Serv_HSP_List()
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,Name FROM Depts   WHERE  convert(nvarchar(255),ParentID) IS null";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


     








        public DataSet PM_TPL_Info(string TID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select PM项目表.项目,PM模板项目表.工时,PM模板项目表.难度,PM模板项目表.内容,PM模板项目表.周期 FROM  PM模板项目表 JOIN PM项目表 ON  convert(nvarchar(255),PM模板项目表.项目ID)=convert(nvarchar(255),PM项目表.ID)   WHERE convert(nvarchar(255),模板ID)= '" + TID + "'";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet PM_TPL_List()
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select * from PM模板表 ";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }






/// <summary>
/// PM相关方法
/// </summary>
/// <param name="Department"></param>
/// <param name="Hospital"></param>
/// <returns></returns>



        public DataSet PM_list_UnFinish_By_Department(string Department)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 任务表.ID,任务表.Flag,设备表.Flag,设备表.设备名称,任务表.故障描述,任务表.故障代码,任务表.报修人,任务表.报修时间,任务表.回应时间,任务表.回应人,任务表.维修进程,任务表.图片数量,任务表.音频数量,任务表.所属科室,任务表.所属医院,任务表.任务类别,任务表.执行时间 FROM 设备表 JOIN 任务表  ON  convert(nvarchar(255),任务表.设备Flag)=convert(nvarchar(255),设备表.Flag) WHERE convert(nvarchar(255),设备表.所属科室)= '" + Department + "'AND convert(nvarchar(255),任务表.维修进程)!= '完成' AND convert(nvarchar(255),任务表.任务类别)= 'PM' AND convert(nvarchar(255),任务表.执行时间)<'201407' order by 任务表.ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


       


        public DataSet Camera_List(string HSPID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select* FROM 视频监控表 WHERE convert(nvarchar(255),HSPID)= '" + HSPID + "'";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public String PM_Creat()
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();
            string month = DateTime.Today.Month.ToString();
            if (DateTime.Today.Month.ToString().Length < 2)
                month = "0" + DateTime.Today.Month.ToString();
            String TD = DateTime.Today.Year.ToString() + month + "31";


            string selectStr = "select 设备Flag,执行人,模板ID FROM 设备PM计划表 WHERE  convert(nvarchar(255),下次维护日期)<='" + TD + "' order by ID";
            
            ds = db.RunProcReturnDS(selectStr);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

            {
                Add_PM_Task_By_EQFlag(ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][2].ToString(), ds.Tables[0].Rows[i][1].ToString());


                
            
            
            }
      




            return ds.Tables[0].Rows.Count.ToString();


        }

      



        public string Add_PM_Task_By_EQFlag(string EQFlag,  string TID, string OPPersonID)
        {
            DateTime TD = DateTime.Now;
            string mtyear = TD.Year.ToString();
            string mtmonth = null;
            string mtday = null;
            mtmonth = TD.Month.ToString();
            mtday = TD.Day.ToString();
            if (TD.Month.ToString().Length == 1)
                mtmonth = "0" + TD.Month.ToString();

            if (TD.Day.ToString().Length == 1)
                mtday = "0" + TD.Day.ToString();

            String Today = mtyear + mtmonth + mtday;
            Change_PM_Date( EQFlag);
            DataBase db = new DataBase();

            int i = db.RunProc("insert into PM任务表 (EQFlag,是否完成,模板ID,执行人ID,生成日期) values('" + EQFlag + "','否','" + TID + "','" + OPPersonID + "','" + Today + "')");

            return i.ToString();
            
        }

        public void Change_PM_Date(string EQFlag)
        {
            DataBase db = new DataBase();
            DataSet ds = db.RunProcReturnDS("select 周期 from 设备PM计划表 where 设备Flag='" + EQFlag + "'");

            String mt = ds.Tables[0].Rows[0][0].ToString();

            DateTime Daytemp = DateTime.Now.AddMonths(int.Parse(mt));
            string mtyear = Daytemp.Year.ToString();
            string mtmonth = null;
            string mtday = null;
            mtmonth = Daytemp.Month.ToString();
            mtday = Daytemp.Day.ToString();
            if (Daytemp.Month.ToString().Length == 1)
                mtmonth = "0" + Daytemp.Month.ToString();

            if (Daytemp.Day.ToString().Length == 1)
                mtday = "0" + Daytemp.Day.ToString();


            String PMDay = mtyear + mtmonth + mtday;
            db.RunProc("update 设备PM计划表 set 下次维护日期 ='" + PMDay + "' where 设备Flag='" + EQFlag + "'");

        }






        public string Set_Service_Surport(string TaskFlag, String ServiceID)
        {

            DataBase db = new DataBase();
            db.RunProc("update 任务表 set 技术支持ID ='" + ServiceID + "' where Flag='" + TaskFlag + "'");
            return "ok";

        }
        public String EQ_PM_Creat()
        {
            DateTime TD = DateTime.Now;
            string mtyear = TD.Year.ToString();
            string mtmonth = null;
            string mtday = null;
            mtmonth = TD.Month.ToString();
            mtday = TD.Day.ToString();
            if (TD.Month.ToString().Length == 1)
                mtmonth = "0" + TD.Month.ToString();

            if (TD.Day.ToString().Length == 1)
                mtday = "0" + TD.Day.ToString();

            String Today = mtyear + mtmonth + mtday;
            DataBase db = new DataBase();
            int j = 0;
            string selectStr = "select Flag  FROM 设备表 ";
            DataSet ds = db.RunProcReturnDS(selectStr);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)


            {
               

                DataBase db2 = new DataBase();

                j=db.RunProc("insert into 设备PM计划表(设备Flag) values('" + ds.Tables[0].Rows[i][0].ToString() + "')"); 
            
            }

            return j.ToString();
            
        }
      
        public string Add_PM_Log(string Flag,string PMID, string TItemID, string Content)
        {
            String TD = Get_Today();

            DataBase db = new DataBase();


            String eqflag = db.RunProcReturnDtable("select EQFlag from PM任务表 where ID='" + PMID + "'").Rows[0][0].ToString();
            String pr = db.RunProcReturnDtable("select 周期 from PM模板项目表 where ID='" + TItemID + "'").Rows[0][0].ToString();
            string nextpmdate = Get_NextPM_Date( pr);

            int i = db.RunProc("insert into PM记录表 (Flag,EQFlag,PMID,模板项目ID,记录内容,记录时间,下次检测日期) values('" + Flag + "','" + eqflag + "','" + PMID + "','" + TItemID + "','" + Content + "','" + TD + "','" + nextpmdate + "')");




            return i.ToString();



        }






        public string Get_NextPM_Date(string period)
        {
            DataBase db = new DataBase();

            DateTime Daytemp = DateTime.Now.AddMonths(int.Parse(period));
            string mtyear = Daytemp.Year.ToString();
            string mtmonth = null;
            string mtday = null;
            mtmonth = Daytemp.Month.ToString();
            mtday = Daytemp.Day.ToString();
            if (Daytemp.Month.ToString().Length == 1)
                mtmonth = "0" + Daytemp.Month.ToString();

            if (Daytemp.Day.ToString().Length == 1)
                mtday = "0" + Daytemp.Day.ToString();


            String PMDay = mtyear + mtmonth + mtday;
            return PMDay;

        }



        public string Get_Today()
        {
            DataBase db = new DataBase();




            DateTime Daytemp = DateTime.Now;
            string year = Daytemp.Year.ToString();
            string month = null;
            string day = null;
            month = Daytemp.Month.ToString();
            day = Daytemp.Day.ToString();
            if (Daytemp.Month.ToString().Length == 1)
                month = "0" + Daytemp.Month.ToString();

            if (Daytemp.Day.ToString().Length == 1)
                day = "0" + Daytemp.Day.ToString();


            return year + month + day;
            

        }









  

        public string Task_Approve(string TaskFlag, string Approve, string ApproveContent,string ApproveUserID)
        {

            DataBase db = new DataBase();

            db.RunProc("update 报修任务表 set 审批意见 ='" + Approve + "',审批附言 ='" + ApproveContent + "',审批人 ='" + ApproveUserID + "' where Flag='" + TaskFlag + "'");
            return "ok";

        }

        public string Set_PM_Plan(string EQFlag, string year, string month, string day, string Period)
        {

            DataBase db = new DataBase();
            if (month.Length == 1)
                month = "0" + month;
            if (day.Length == 1)
                day = "0" + day;
            String NextDate = year + month + day;
            db.RunProc("update 设备表 set 下次PM日期 ='" + NextDate + "',PM周期 ='" + Period + "' where Flag='" + EQFlag + "'");
            return "ok";

        }


      


        public string Set_Task_Execution_EGN(string TaskFlag, String EGNID)
        {

            DataBase db = new DataBase();
            db.RunProc("update 报修任务表 set 执行人 ='" + EGNID + "' where Flag='" + TaskFlag + "'");
            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();


            string selectStr = "select WeChat FROM Users WHERE convert(nvarchar(255),ID)= '" + EGNID + "'";
            ds = db2.RunProcReturnDS(selectStr);
            SendMSG sd = new SendMSG();
            return sd.Send(ds.Tables[0].Rows[0][0].ToString(), " 您有新的维修任务，请查看");
             

        }



    
        public string Change_Task_State(string TaskFlag,string State)
        {

            DataBase db = new DataBase();
            db.RunProc("update 报修任务表 set 维修进程 ='" + State + "' where Flag='" + TaskFlag + "'");

            DataBase db2 = new DataBase();
            DataSet ds = new DataSet();


            string selectStr = "select 报修人微信UserID FROM 报修任务表 WHERE convert(nvarchar(255),Flag)= '" + TaskFlag + "'";
            ds = db2.RunProcReturnDS(selectStr);
            SendMSG sd = new SendMSG();
            string rs = sd.Send(ds.Tables[0].Rows[0][0].ToString(), " 您的报修有新的进展:" + State);


            return "ok";

        }

        public string Change_EQ_Location(string EQFlag, string Location)
        {

            DataBase db = new DataBase();
            db.RunProc("update 设备表 set 存放位置 ='" + Location + "' where Flag='" + EQFlag + "'");
            return "ok";

        }






       

      


        public DataSet PM_List_By_EQFlag(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  * from PM任务表 where convert(nvarchar(255),EQFlag)= '" + EQFlag + "'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }




/// <summary>
/// //Repair相关
/// </summary>
/// <param name="EQFlag"></param>
/// <returns></returns>




      

        public DataSet Task_list_All(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select* FROM 报修任务表 WHERE convert(nvarchar(255),设备Flag)= '" + EQFlag + "' order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public DataSet Repair_list_All(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 报修任务表.ID,报修任务表.Flag,报修任务表.报修时间,报修任务表.维修进程,Users.ChineseName,报修任务表.故障描述 FROM 报修任务表 Join Users ON convert(nvarchar(255),报修任务表.执行人)= convert(nvarchar(255),Users.ID) WHERE convert(nvarchar(255),报修任务表.设备Flag)= '" + EQFlag + "'and  convert(nvarchar(255),报修任务表.任务类别)<> 'PM'  order by 报修任务表.ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


        public DataSet Repair_Image_list(string RPFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,任务Flag,文件名,文件类型  FROM 任务图片表 WHERE convert(nvarchar(255),任务Flag)= '" + RPFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Task_Image_list(string TSKFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,Flag,文件名,文件类型  FROM  报修任务文件表 WHERE convert(nvarchar(255),Flag)= '" + TSKFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public DataSet Task_Log_Image_list(string TSKFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,Flag,文件名,文件类型  FROM  报修任务记录文件表 WHERE convert(nvarchar(255),Flag)= '" + TSKFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public String Get_Log_Image(string LogFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 文件名  FROM 记录图片表 WHERE convert(nvarchar(255),LogFlag)= '" + LogFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows[0][0].ToString();


        }
       

        public String Get_PM_Log_Image(string PMLogFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 文件名  FROM PM记录图片表 WHERE convert(nvarchar(255),PMLogFlag)= '" + PMLogFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows[0][0].ToString();


        }

        public DataSet Spare_Image_list(string SpareFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,Flag,文件名  FROM 配件图片表 WHERE convert(nvarchar(255),Flag)= '" + SpareFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }




        public DataSet Measure_Image_list(string EQFlag,string mYear)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,设备Flag,文件名,文件描述,上传时间  FROM 设备证书表 WHERE convert(nvarchar(255),设备Flag)= '" + EQFlag + "'and  convert(nvarchar(255),文件描述)= '" + mYear + "'+'年度计量证书' and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }









        public DataSet EQ_Image_list(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,设备Flag,文件名  FROM 设备图片表 WHERE convert(nvarchar(255),设备Flag)= '" + EQFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID desc";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet EQ_Cer_List(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,设备Flag,文件名,文件描述,上传时间  FROM 设备证书表 WHERE convert(nvarchar(255),设备Flag)= '" + EQFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet EQ_Meas_Cer_List(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,设备Flag,文件名,文件描述,上传时间  FROM 设备证书表 WHERE convert(nvarchar(255),设备Flag)= '" + EQFlag + "'and  convert(nvarchar(255),删除标识)= '0'and  convert(nvarchar(255),文件描述) Like  '%'+'计量证书'+'%' order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet DEL_EQ_Cer_By_Flag(string CerID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "delete from 设备证书表 where ID='" + CerID + "'";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet EQ_Measurement_List(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select * FROM 计量检定表 WHERE convert(nvarchar(255),EQFlag)= '" + EQFlag + "'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }







        public DataSet EQ_Document_list(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,设备Flag,文件名,文件描述,上传时间  FROM 设备文档表 WHERE convert(nvarchar(255),设备Flag)= '" + EQFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public string Repair_Image_Count(string RPFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID FROM 任务图片表 WHERE convert(nvarchar(255),任务Flag)= '" + RPFlag + "'and  convert(nvarchar(255),删除标识)= '0' ";
            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows.Count.ToString();
;
           


        }

        public string PM_Image_Count(string PM_Log_Flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID FROM PM记录图片表 WHERE convert(nvarchar(255),PMLogFlag)= '" + PM_Log_Flag + "'and  convert(nvarchar(255),删除标识)= '0' ";
            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows.Count.ToString();
            ;
            


        }

      

        public DataSet Repair_Video_list(string RPFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,任务Flag,文件名  FROM 任务视频表 WHERE convert(nvarchar(255),任务Flag)= '" + RPFlag + "'and  convert(nvarchar(255),删除标识)= '0'  order by ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public string Repair_Video_Count(string RPFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID FROM 任务视频表 WHERE convert(nvarchar(255),任务Flag)= '" + RPFlag + "'and  convert(nvarchar(255),删除标识)= '0' ";
            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows.Count.ToString();


        }

        public string EQ_Image_Count(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID FROM 设备图片表 WHERE convert(nvarchar(255),设备Flag)= '" + EQFlag + "'and  convert(nvarchar(255),删除标识)= '0' ";
            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows.Count.ToString();


        }
        public string Spare_Image_Count(string SpareFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID FROM 配件图片表 WHERE convert(nvarchar(255),Flag)= '" + SpareFlag + "'and  convert(nvarchar(255),删除标识)= '0' ";
            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows.Count.ToString();


        }


        public string EQ_Cer_Count(string EQFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select ID FROM 设备证书表 WHERE convert(nvarchar(255),设备Flag)= '" + EQFlag + "'and  convert(nvarchar(255),删除标识)= '0' ";
            ds = db.RunProcReturnDS(selectStr);
            return ds.Tables[0].Rows.Count.ToString();


        }




        public DataSet Repair_Info_By_Flag(string flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  * from 任务表 where convert(nvarchar(255),Flag)= '" + flag + "' ";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


        public DataSet PM_Info_By_ID(string PMID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  * from PM任务表 where convert(nvarchar(255),ID)= '" + PMID + "' ";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }



        public DataSet get_TPL_Item_List(string PMID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();
            
            string selectStr = "select  * from PM模板项目表 where convert(nvarchar(255),模板ID)= '" + PMID + "' ";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


        public DataSet get_TPL_Item_Log_List(string PMID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  * from PM模板项目表 where convert(nvarchar(255),模板ID)= '" + PMID + "' ";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }




        public DataSet Spare_Info_By_Flag(string SpareFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  * from 配件表 where convert(nvarchar(255),Flag)= '" + SpareFlag + "' ";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


        public DataSet Measure_Info_By_Flag(string Flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  ID,Flag, EQFlag,证书编号,年度,检定单位,检定日期,价格,备注,是否合格,所属医院 from 计量检定表 where convert(nvarchar(255),Flag)= '" + Flag + "' ";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }



        /// <summary>
        /// ///////////////////////////////////////////////////////////////////////
        /// </summary>
        /// <param name="department"></param>
        /// <param name="hospital"></param>
        /// <returns></returns>









        public DataSet EQ_Info_By_WXNr(string ID)
        {

            DataBase db = new DataBase();



            string EQFlag =ID;

            DataSet ds = new DataSet();


            string selectStr2 = "select 设备表.ID,设备表.Flag,设备表.设备名称,设备表.设备型号,设备表.出厂编号,设备表.生产厂商,设备表.购入日期,Depts.Name,Depts.DeptNumber,设备表.Flag FROM 设备表 JOIN Depts ON  convert(nvarchar(255),设备表.所属科室)= convert(nvarchar(255),Depts.DeptNumber) where convert(nvarchar(255),Flag)= '" + EQFlag + "'"; ;

            ds = db.RunProcReturnDS(selectStr2);


            return ds;




        }

 


        public DataSet EQ_Info_By_EQFlag(string flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 设备表.ID, 设备表.Flag, 设备表.设备编号, 设备表.设备名称,设备表.出厂编号, 设备表.设备型号, 设备表.生产厂商, 设备表.经销商, 设备表.购入价格, 设备表.购入日期,设备表.所属科室, 设备表.所属医院,设备表.设备类型, 设备表.资产类型, 设备表.厂商工程师, 设备表.医院工程师, 设备表.设备负责人, 设备表.售后电话, 设备表.备注, 设备表.图片数量,设备表.存放位置, 设备表.计量标识 ,设备表.下次PM日期,设备表.PM周期,设备表.证书数量,设备表.机房ID,设备表.管理科室,Users.ChineseName,Users.CellPhone,Users.Name,报修方式,设备表.报修接收人1,设备表.报修接收人2,设备表.报修接收人3  from 设备表  JOIN Users ON  convert(nvarchar(255),设备表.医院工程师)= convert(nvarchar(255),Users.ID)  where convert(nvarchar(255),设备表.Flag)= '" + flag + "'";




            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)
            {

                if (!ds.Tables[0].Rows[0][31].ToString().Trim().Equals("0"))
                {
                    DataSet ds2 = new DataSet();
                    string selectStr2 = "select Name FROM Users  where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[0][31] + "'";
                    ds2 = db.RunProcReturnDS(selectStr2);
                    ds.Tables[0].Rows[0][31] = ds2.Tables[0].Rows[0][0];
                }


                if (!ds.Tables[0].Rows[0][32].ToString().Trim().Equals("0"))
                {
                    DataSet ds3 = new DataSet();
                    string selectStr3 = "select Name FROM Users  where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[0][32] + "'";
                    ds3 = db.RunProcReturnDS(selectStr3);
                    ds.Tables[0].Rows[0][32] = ds3.Tables[0].Rows[0][0];

                }

                if (!ds.Tables[0].Rows[0][33].ToString().Trim().Equals("0"))
                {

                    DataSet ds4 = new DataSet();
                    string selectStr4 = "select Name FROM Users  where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[0][33] + "'";
                    ds4 = db.RunProcReturnDS(selectStr4);
                    ds.Tables[0].Rows[0][33] = ds4.Tables[0].Rows[0][0];

                }
                ds.AcceptChanges();
                return ds;
            }


            else
                return ds;


            


        }
        public DataSet EQ_Info_By_EQNr(string Nr)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 设备表.ID, 设备表.Flag, 设备表.设备编号, 设备表.设备名称,设备表.出厂编号, 设备表.设备型号, 设备表.生产厂商, 设备表.经销商, 设备表.购入价格, 设备表.购入日期,设备表.所属科室, 设备表.所属医院,设备表.设备类型, 设备表.资产类型, 设备表.厂商工程师, 设备表.医院工程师, 设备表.设备负责人, 设备表.售后电话, 设备表.备注, 设备表.图片数量,设备表.存放位置, 设备表.计量标识 ,设备表.下次PM日期,设备表.PM周期,设备表.证书数量,设备表.机房ID,设备表.管理科室,Users.ChineseName,Users.CellPhone,Users.Name,报修方式,设备表.报修接收人1,设备表.报修接收人2,设备表.报修接收人3  from 设备表  JOIN Users ON  convert(nvarchar(255),设备表.医院工程师)= convert(nvarchar(255),Users.ID)  where convert(nvarchar(255),设备表.设备编号)= '" + Nr + "'";




            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)
            {

                if (!ds.Tables[0].Rows[0][31].ToString().Trim().Equals("0"))
                {
                    DataSet ds2 = new DataSet();
                    string selectStr2 = "select Name FROM Users  where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[0][31] + "'";
                    ds2 = db.RunProcReturnDS(selectStr2);
                    ds.Tables[0].Rows[0][31] = ds2.Tables[0].Rows[0][0];
                }


                if (!ds.Tables[0].Rows[0][32].ToString().Trim().Equals("0"))
                {
                    DataSet ds3 = new DataSet();
                    string selectStr3 = "select Name FROM Users  where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[0][32] + "'";
                    ds3 = db.RunProcReturnDS(selectStr3);
                    ds.Tables[0].Rows[0][32] = ds3.Tables[0].Rows[0][0];

                }

                if (!ds.Tables[0].Rows[0][33].ToString().Trim().Equals("0"))
                {

                    DataSet ds4 = new DataSet();
                    string selectStr4 = "select Name FROM Users  where convert(nvarchar(255),ID)= '" + ds.Tables[0].Rows[0][33] + "'";
                    ds4 = db.RunProcReturnDS(selectStr4);
                    ds.Tables[0].Rows[0][33] = ds4.Tables[0].Rows[0][0];

                }
                ds.AcceptChanges();
                return ds;
            }


            else
                return ds;





        }

        public DataSet Repair_Log_Info_By_Flag(string flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  * from 维护记录表 where convert(nvarchar(255),Flag)= '" + flag + "'";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet PM_Log_Info_By_Flag(string flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select  * from PM记录表 where convert(nvarchar(255),Flag)= '" + flag + "'";

            ds = db.RunProcReturnDS(selectStr);


            string sl2="select  ID from PM记录图片表 where convert(nvarchar(255),PMLogFlag)= '" + flag + "'";
            DataSet ds2 = db.RunProcReturnDS(sl2);
            ds.Tables[0].Rows[0][7] = ds2.Tables[0].Rows.Count.ToString();


            string sl3 = "select  ID from PM记录音频表 where convert(nvarchar(255),PMLogFlag)= '" + flag + "'";
            DataSet ds3 = db.RunProcReturnDS(sl3);
            ds.Tables[0].Rows[0][8] = ds3.Tables[0].Rows.Count.ToString();









            ds.AcceptChanges();

            return ds;




            
        }


        public DataSet Log_List_By_Flag(string flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select 报修任务记录表.Flag,报修任务记录表.记录内容,报修任务记录表.记录人,报修任务记录表.记录时间 from 报修任务记录表  where convert(nvarchar(255),报修任务记录表.Task_Flag)= '" + flag + "' order by ID desc";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


      

        public DataSet Spare_List_By_Task_Flag(string TaskFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select 配件表.ID,配件表.Flag, 配件表.Name,配件表.Type,配件表.SrNr, 配件表.Price, 配件表.NeworOld,配件表.PISDate,配件表.GOSDate,配件表.HospitalID  from 配件更换表 JOIN 配件表 ON  convert(nvarchar(255),配件更换表.配件Flag)= convert(nvarchar(255),配件表.Flag) where convert(nvarchar(255),配件更换表.任务Flag)= '" + TaskFlag + "' order by ID desc ";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


        public string Spare_Cost_By_Task_Flag(string TaskFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select 配件表.Price from 配件更换表 JOIN 配件表 ON  convert(nvarchar(255),配件更换表.配件Flag)= convert(nvarchar(255),配件表.Flag) where convert(nvarchar(255),配件更换表.任务Flag)= '" + TaskFlag + "' order by 配件更换表.ID desc";



            ds = db.RunProcReturnDS(selectStr);
            int Sum=0;
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                Sum+=Int32.Parse(ds.Tables[0].Rows[i][0].ToString());


            }





            return Sum.ToString();




        }



        public string Task_Total_Cost_By_Task_Flag(string TaskFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select 配件表.Price from 配件更换表  JOIN 配件表 ON  convert(nvarchar(255),配件更换表.配件Flag)= convert(nvarchar(255),配件表.Flag) where convert(nvarchar(255),配件更换表.任务Flag)= '" + TaskFlag + "' order by 配件更换表.ID desc";



            ds = db.RunProcReturnDS(selectStr);
            int SpareSum = 0;
            int RPCost=0;
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                SpareSum += Int32.Parse(ds.Tables[0].Rows[i][0].ToString());


            }

            string selectStr2 = "select 维修费,其它费 From 费用表 Where convert(nvarchar(255),任务Flag)='" + TaskFlag + "'";
            DataBase db2 = new DataBase();
            DataSet ds2 = new DataSet();

            ds2 = db2.RunProcReturnDS(selectStr2);
            if (ds2.Tables[0].Rows.Count > 0)
            {

                RPCost = Int32.Parse(ds2.Tables[0].Rows[0][0].ToString()) + Int32.Parse(ds2.Tables[0].Rows[0][1].ToString());
            
            
            }


            return (RPCost + SpareSum).ToString();




        }












        public DataSet Spare_Present_List_By_HSPID(string HSPID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select * from 配件表 where convert(nvarchar(255),HospitalID)='" + HSPID + "' AND convert(nvarchar(255),GOSDate)= '0' order by ID desc";



            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }



        public String Get_Service_Spare_Info(string Flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 配件介绍  FROM 销售配件表 WHERE convert(nvarchar(255),Flag)= '" + Flag + "'";
            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)

                return ds.Tables[0].Rows[0][0].ToString();
            else
                return "0";


        }
       










/// <summary>
/// RepairLog
/// </summary>
/// <param name="Flag"></param>
/// <param name="RP_Flag"></param>
/// <param name="photocount"></param>
/// <param name="voicecount"></param>
/// <param name="content"></param>
/// <param name="recordperson"></param>
/// <param name="recordtime"></param>
/// <param name="logtype"></param>
/// <param name="hospital"></param>
/// <returns></returns>

        public string GetTimeNow()


        {

            return DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString();
        
        }
        public string GetDate8()
        {

            string Year = DateTime.Now.Year.ToString();
            string Month = DateTime.Now.Month.ToString();
            if (Month.Length < 2)
                Month = "0" + Month;
            string Day=DateTime.Now.Day.ToString();
            if (Day.Length < 2)
                Day = "0" + Day;
            return Year + Month + Day;

        }
        public string GetYearMonth()
        {
            string Year = DateTime.Now.Year.ToString();
            string Month = DateTime.Now.Month.ToString();
            if (Month.Length < 2)
                Month = "0" + Month;
          
            return Year + Month + "00";

        }


        public string Add_Repair_Log(string Flag, string RP_Flag, string photocount, string voicecount, string content, string recordperson, string recordtime, string logtype,string hospitalID)
        {
           
            DataBase db = new DataBase();

       
            int result = db.RunProc("insert into 报修任务记录表 (Flag,任务Flag,图片数量,音频数量,记录内容,记录人,记录时间,记录类型,所属医院) values('" + Flag + "','" + RP_Flag + "','" + photocount + "','" + voicecount + "','" + content + "','" + recordperson + "','" + GetTimeNow() + "','" + logtype + "','" + hospitalID + "')");

            return result.ToString();

        }


        public int AddEQ(string flag, string EQNr, string name, string SrNr, string Type, string Factory, string Distributor, string Price, string BuyDate, string Department, string Hspital, string EQType, string AssetsType, string FctEngineerID, string HspEngineerID, string Admin, string Service_TEL, string Remarks, string ImageCount, string Location, string Measurement_Flag)
        {

            if (flag.Equals(""))
                flag = "无";
            if (EQNr.Equals(""))
                EQNr = "无";
            if (name.Equals(""))
                name = "无";           //设备名称

            if (SrNr.Equals(""))
                SrNr = "无";
            if (Type.Equals(""))
                Type = "无";
            if (Factory.Equals(""))
                Factory = "无";
            if (Distributor.Equals(""))
                Distributor = "无";

            if (Price.Equals(""))

                Price = "0";
            if (BuyDate.Equals(""))
                BuyDate = "无";
            if (Department.Equals(""))
                Department = "无";
            if (Hspital.Equals(""))
                Hspital = "无";
            if (EQType.Equals(""))

                EQType = "无";
            if (AssetsType.Equals(""))
                AssetsType = "无";
            if (FctEngineerID.Equals(""))

                FctEngineerID = "无";
            if (HspEngineerID.Equals(""))
                HspEngineerID = "无";
            if (Admin.Equals(""))
                Admin = "无";
            if (Service_TEL.Equals(""))
                Service_TEL = "无";
            if (Remarks.Equals(""))
                Remarks = "无";



            DataBase db = new DataBase();
            return db.RunProc("insert into 设备表 (Flag,设备编号,设备名称,出厂编号,设备型号,生产厂商,经销商,购入价格,购入日期,所属科室,所属医院,设备类型,资产类型,厂商工程师,医院工程师,设备负责人,售后电话,备注,图片数量,存放位置,计量标识) values('" + flag + "','" + EQNr + "','" + name + "','" + SrNr + "','" + Type + "','" + Factory + "','" + Distributor + "','" + Price + "','" + BuyDate + "','" + Department + "','" + Hspital + "','" + EQType + "','" + AssetsType + "','" + FctEngineerID + "','" + HspEngineerID + "','" + Admin + "','" + Service_TEL + "','" + Remarks + "' ,'" + ImageCount + "','" + Location + "','" + Measurement_Flag + "')");
        }

        public int AddEQ_Full(string flag, string EQNr, string name, string SrNr, string Type, string Factory, string Distributor, string Price, string BuyDate, string Department, string Hspital, string EQType, string AssetsType, string FctEngineerID, string HspEngineerID, string Admin, string Service_TEL, string Remarks, string ImageCount, string Location, string Measurement_Flag, string PM_Next, string PM_Period, string Certi_Count, string Admin_Department, string TempFlag)
        {
             DataBase db1 = new DataBase();

            DataSet ds1 = new DataSet();


            string selectStr = "select ID from 设备表 where convert(nvarchar(255),Flag)= '" + flag + "'";

            ds1 = db1.RunProcReturnDS(selectStr);
            if (ds1.Tables[0].Rows.Count == 0)
            {

                if (flag.Equals(""))
                    flag = "无";
                if (EQNr.Equals(""))
                    EQNr = "无";
                if (name.Equals(""))
                    name = "无";           //设备名称

                if (SrNr.Equals(""))
                    SrNr = "无";
                if (Type.Equals(""))
                    Type = "无";
                if (Factory.Equals(""))
                    Factory = "无";
                if (Distributor.Equals(""))
                    Distributor = "无";

                if (Price.Equals(""))

                    Price = "0";
                if (BuyDate.Equals(""))
                    BuyDate = "无";
                if (Department.Equals(""))
                    Department = "无";
                if (Hspital.Equals(""))
                    Hspital = "无";
                if (EQType.Equals(""))

                    EQType = "无";
                if (AssetsType.Equals(""))
                    AssetsType = "无";
                if (FctEngineerID.Equals(""))

                    FctEngineerID = "无";
                if (HspEngineerID.Equals(""))
                    HspEngineerID = "无";
                if (Admin.Equals(""))
                    Admin = "无";
                if (Service_TEL.Equals(""))
                    Service_TEL = "无";
                if (Remarks.Equals(""))
                    Remarks = "无";
                if (Remarks.Equals(""))
                    Remarks = "无";
                if (ImageCount.Equals(""))
                    ImageCount = "0";
                if (Location.Equals(""))
                    Location = "无";
                if (Measurement_Flag.Equals(""))
                    Measurement_Flag = "否";
                if (PM_Next.Equals(""))
                    PM_Next = "无";
                if (PM_Period.Equals(""))
                    PM_Period = "0";
                if (Certi_Count.Equals(""))
                    Certi_Count = "0";
                if (Admin_Department.Equals(""))
                    Admin_Department = "无";
                if (TempFlag.Equals(""))
                    TempFlag = "0";

                DataBase db = new DataBase();




                return db.RunProc("insert into 设备表 (Flag,设备编号,设备名称,出厂编号,设备型号,生产厂商,经销商,购入价格,购入日期,所属科室,所属医院,设备类型,资产类型,厂商工程师,医院工程师,设备负责人,售后电话,备注,图片数量,存放位置,计量标识,下次PM日期,PM周期,证书数量,管理科室,临时设备) values('" + flag + "','" + EQNr + "','" + name + "','" + SrNr + "','" + Type + "','" + Factory + "','" + Distributor + "','" + Price + "','" + BuyDate + "','" + Department + "','" + Hspital + "','" + EQType + "','" + AssetsType + "','" + FctEngineerID + "','" + HspEngineerID + "','" + Admin + "','" + Service_TEL + "','" + Remarks + "' ,'" + ImageCount + "','" + Location + "','" + Measurement_Flag + "','" + PM_Next + "','" + PM_Period + "','" + Certi_Count + "','" + Admin_Department + "','" + TempFlag + "')");

            }
            else
                return 0;
            
            }

        public int Add_User_Head_image(string UserID, string filename)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into 用户头像表 (UserID,文件名,上传时间) values('" + UserID + "','" + filename + "','" + t + "')");

            return i;
        }
        public int Add_EQ_image(string EQFlag, string filename)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into 设备图片表 (设备Flag,文件名,上传时间) values('" + EQFlag + "','" + filename + "','" + t + "')");

            return i;
        }

        public int Add_Adress(string Dept, string Name,string Nr)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into Mobile (部门,姓名,电话) values('" + Dept + "','" + Name + "','" + Nr + "')");

            return i;
        }
        public int Add_office_Adress(string office, string nr, string dept, string deptkind,string hsp)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into Office (办公室,电话,科室,科室分类,院区) values('" + office + "','" + nr + "','" + dept + "','" + deptkind + "','" + hsp + "')");

            return i;
        }
        public int Add_EQ_Cer_image(string EQFlag, string filename, string describe)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into 设备证书表 (设备Flag,文件名,文件描述,上传时间) values('" + EQFlag + "','" + filename + "','" + describe + "','" + t + "')");

            return i;
        }

        public int Add_EQ_Doc_image(string EQFlag, string filename, string describe)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into 设备文档表 (设备Flag,文件名,文件描述,上传时间) values('" + EQFlag + "','" + filename + "','" + describe + "','" + t + "')");

            return i;
        }

        public int Add_Task_image(string TaskFlag, string filename,string FileType)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into 任务图片表(任务Flag,文件名,上传时间,文件类型) values('" + TaskFlag + "','" + filename + "','" + t + "','" + FileType + "')");

            return i;
        }

        public int Add_Log_image(string LogFlag, string filename)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into 记录图片表(LogFlag,文件名,上传时间) values('" + LogFlag + "','" + filename + "','" + t + "')");

            return i;
        }


        public int Add_PM_Log_image(string PMLogFlag, string filename)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into PM记录图片表(PMLogFlag,文件名,上传时间) values('" + PMLogFlag + "','" + filename + "','" + t + "')");

            return i;
        }



       
      

      


     










        public int Add_Task_Video(string TaskFlag, string filename)
        {

            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into 任务视频表(任务Flag,文件名,上传时间) values('" + TaskFlag + "','" + filename + "','" + t + "')");

            return i;
        }


        public string Add_Task(string Flag, string EQFlag, string Describe, string FaultCode, string CallPerson, string CallTime, string AnswerTime, string TaskPersonID, string Progress, string PictureCount, string VoiceCount, string VideoCount,string Department, string Hospital, string Type,string Repair_Cost)
        {
    
            DataBase db = new DataBase();
            int i=0;
          //  if(EQFlag.Equals("000000"))
                i = db.RunProc("insert into 报修任务表 (Flag,设备Flag,故障描述,故障代码,报修人,报修时间,回应时间,执行人,维修进程,图片数量,音频数量,视频数量,所属科室,所属医院,任务类别,维修费,生成日期) values('" + Flag + "','" + EQFlag + "','" + Describe + "','" + FaultCode + "','" + CallPerson + "','" + DateTime.Now.ToLocalTime().ToString() + "','" + AnswerTime + "','" + TaskPersonID + "','" + Progress + "','" + PictureCount + "','" + VoiceCount + "','" + VideoCount + "','" + Department + "','" + Hospital + "','" + Type + "','" + Repair_Cost + "','" + GetTimeNow() + "' )");
          //  else
               // i = db.RunProc("insert into 任务表 (Flag,设备Flag,故障描述,故障代码,报修人,报修时间,回应时间,执行人,维修进程,图片数量,音频数量,视频数量,所属科室,所属医院,任务类别,维修费,生成日期) values('" + Flag + "','" + EQFlag + "','" + Describe + "','" + FaultCode + "','" + CallPerson + "','" + DateTime.Now.ToLocalTime().ToString() + "','" + AnswerTime + "','" + TaskPersonID + "','" + Progress + "','" + PictureCount + "','" + VoiceCount + "','" + VideoCount + "','" + Department + "','" + Hospital + "','" + Type + "','" + Repair_Cost + "','" + GetTimeNow() + "')");
            return i.ToString();
        }
        public string Add_Task_Win(string Flag, string EQFlag, string Describe, string FaultCode, string CallPerson, string CallTime, string AnswerTime, string TaskPersonID, string Progress, string PictureCount, string VoiceCount, string VideoCount, string Department, string Hospital, string Type, string Repair_Cost)
        {

            DataBase db = new DataBase();
            int i = 0;
            //  if(EQFlag.Equals("000000"))
            i = db.RunProc("insert into 报修任务表 (Flag,设备Flag,故障描述,故障代码,报修人,报修时间,回应时间,执行人,维修进程,图片数量,音频数量,视频数量,所属科室,所属医院,任务类别,维修费,生成日期) values('" + Flag + "','" + EQFlag + "','" + Describe + "','" + FaultCode + "','" + CallPerson + "','" + DateTime.Now.ToLocalTime().ToString() + "','" + AnswerTime + "','" + TaskPersonID + "','" + Progress + "','" + PictureCount + "','" + VoiceCount + "','" + VideoCount + "','" + Department + "','" + Hospital + "','" + Type + "','" + Repair_Cost + "','" + GetDate8() + "' )");
            //  else
            // i = db.RunProc("insert into 任务表 (Flag,设备Flag,故障描述,故障代码,报修人,报修时间,回应时间,执行人,维修进程,图片数量,音频数量,视频数量,所属科室,所属医院,任务类别,维修费,生成日期) values('" + Flag + "','" + EQFlag + "','" + Describe + "','" + FaultCode + "','" + CallPerson + "','" + DateTime.Now.ToLocalTime().ToString() + "','" + AnswerTime + "','" + TaskPersonID + "','" + Progress + "','" + PictureCount + "','" + VoiceCount + "','" + VideoCount + "','" + Department + "','" + Hospital + "','" + Type + "','" + Repair_Cost + "','" + GetTimeNow() + "')");
            return i.ToString();
        }

        public string Add_Task_iOS(string Flag, string EQFlag, string Describe, string CallPerson, string DeptID, string HSPID)
        {

            DataBase db = new DataBase();
            int i = 0;

            i = db.RunProc("insert into 报修任务表 (Flag,设备Flag,故障描述,报修人,报修时间,所属科室,所属医院,生成日期,维修进程) values('" + Flag + "','" + EQFlag + "','" + Describe + "','" + CallPerson + "','" + GetTimeNow() + "','" + DeptID + "','" + HSPID + "','" + GetTimeNow() + "','等待回应' )");
         return i.ToString();

        }
        public string Add_Task_Log(string Task_Flag, string Log_Flag, string Content, string AddUser, string Hospital)
        {
            string t = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString();
            DataBase db = new DataBase();
            int i = 0;

            i = db.RunProc("insert into 报修任务记录表 (Flag,Task_Flag,记录内容,记录人,记录时间,所属医院) values('" + Log_Flag + "','" + Task_Flag + "','" + Content + "','" + AddUser + "','" + t + "','" + Hospital + "' )");
          return i.ToString();
        }


        public DataSet Task_Log_List(string TaskFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 报修任务记录表.ID,报修任务记录表.Flag,报修任务记录表.Task_Flag,报修任务记录表.记录内容,Users.ChineseName,报修任务记录表.记录时间 FROM 报修任务记录表 Join Users ON convert(nvarchar(255),报修任务记录表.记录人)= convert(nvarchar(255),Users.ID) WHERE convert(nvarchar(255),报修任务记录表.Task_Flag)= '" + TaskFlag + "' order by 报修任务记录表.ID desc";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }



        public void AddEQ_Other(string name, string number, string srnumber, string type, string factory, string buydate, string department, string hospital, string eqtype, string dmperiod, string facengineer, string hspengineer, string picture, string other, string ImageCount)
        {

            DataBase db = new DataBase();


            db.RunProc("insert into 其它设备表 (设备名称,资产编号,出厂编号,设备型号,生产厂家,购买日期,所属科室,所属医院,设备类别,DM周期,厂商工程师,医院工程师,图片标识,备注,图片数量) values('" + name + "','" + number + "','" + srnumber + "','" + type + "','" + factory + "','" + buydate + "','" + department + "','" + hospital + "','" + eqtype + "','" + dmperiod + "','" + facengineer + "','" + hspengineer + "','" + picture + "','" + other + "','" + ImageCount + "')");
        }



        public string EQ_Checked(string EQFlag)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 设备表 set 盘点标识='已盘点' where Flag='" + EQFlag + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;
        }

        public string Task_Rate(string TaskFlag,string Rate,string Content)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 报修任务表 set 服务评价='" + Rate + "',评价内容='" + Content + "' where Flag='" + TaskFlag + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;
        }

        public string EQ_Check_Overage(string EQFlag)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 设备表 set 盘点标识='盘盈' where Flag='" + EQFlag + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;
        }
        public string EQ_Check_Losses(string EQFlag)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 设备表 set 盘点标识='盘亏' where Flag='" + EQFlag + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;
        }
        public string EQ_Add_etc(string EQFlag, string etc)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 设备表 set 备注='"+etc+"' where Flag='" + EQFlag + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;
        }
        public string EQ_Check_Wrong(string EQFlag)
        {
            DataBase dbs = new DataBase();
            string selectStr = "update 设备表 set 盘点标识='错误' where Flag='" + EQFlag + "'";
            string result = dbs.RunProc(selectStr).ToString();
            dbs.Dispose();
            return result;
        }



        public string Task_Total_Cost_by_Month(string year, string month)
        {


            string lastmonth = (Int32.Parse(month) - 1).ToString();
            string nextmonth = (Int32.Parse(month) + 1).ToString();



            if (lastmonth.Length == 1)
                lastmonth = "0" + lastmonth;

            if (nextmonth.Length == 1)
                nextmonth = "0" + nextmonth;

            string last = year + lastmonth + "31";
            string next = year + nextmonth + "01";
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "select Flag from 任务表 where convert(nvarchar(255),生成日期)> '" + last + "' AND convert(nvarchar(255),生成日期)< '" + next + "' order by ID desc";



            ds = db.RunProcReturnDS(selectStr);





            int TotalCost = 0;



            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {


                int Cost = 0;





                string selectStr2 = "select 配件费,维修费,其它费 From 费用表 Where convert(nvarchar(255),任务Flag)='" + ds.Tables[0].Rows[i][0] + "'";
                DataBase db2 = new DataBase();
                DataSet ds2 = new DataSet();

                ds2 = db2.RunProcReturnDS(selectStr2);
                if (ds2.Tables[0].Rows.Count > 0)
                {

                    Cost = Int32.Parse(ds2.Tables[0].Rows[0][0].ToString()) + Int32.Parse(ds2.Tables[0].Rows[0][1].ToString()) + Int32.Parse(ds2.Tables[0].Rows[0][2].ToString());


                }
                TotalCost = TotalCost + Cost;



            }

            return TotalCost.ToString();




        }








        public DataSet RPlistByCallPerson(string username)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 任务表.ID,任务表.报修编号,设备表.设备名称,设备表.资产编号,设备表.出厂编号,设备表.设备型号,设备表.生产厂家,设备表.所属科室,设备表.购买日期,设备表.医院工程师,设备表.厂商工程师,任务表.故障描述,任务表.报修时间,任务表.维修进程,任务表.图片数量 FROM 设备表 JOIN 任务表 ON  convert(nvarchar(255),任务表.资产编号)=convert(nvarchar(255),设备表.资产编号) WHERE convert(nvarchar(255),任务表.报修人)= '" + username + "'AND convert(nvarchar(255),任务表.维修进程)!= '完成' order by 任务表.ID desc";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet RPCall_list_By_EQ(string eqnr)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,报修编号,故障描述,报修时间,维修进程,图片数量,报修人 FROM 任务表 WHERE convert(nvarchar(255),资产编号)= '" + eqnr + "'order by ID desc";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }








        public DataSet RPlist_Egineer(string username)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 任务表.ID,任务表.报修编号,设备表.设备名称,设备表.资产编号,设备表.出厂编号,设备表.设备型号,设备表.生产厂家,设备表.所属科室,设备表.购买日期,设备表.医院工程师,设备表.厂商工程师,任务表.故障描述,任务表.报修时间,任务表.维修进程,任务表.图片数量 FROM 设备表 JOIN 任务表 ON  convert(nvarchar(255),任务表.资产编号)=convert(nvarchar(255),设备表.资产编号) WHERE convert(nvarchar(255),设备表.医院工程师)= '" + username + "'AND convert(nvarchar(255),任务表.维修进程)!= '完成' order by 任务表.ID";
            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }









      

        public DataSet RPlistByCallPerson_Other(string username)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 任务表.ID,任务表.报修编号,其它设备表.设备名称,其它设备表.资产编号,任务表.维修进程,任务表.资产编号 FROM 其它设备表 JOIN 任务表 ON  convert(nvarchar(255),任务表.报修编号)=convert(nvarchar(255),其它设备表.资产编号) WHERE convert(nvarchar(255),任务表.报修人)= '" + username + "'";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        

        public string Add_Spare_Task(string TaskFlag, string SpareFlag, string Replace_Date, string Hospital)
        {

            DataBase db = new DataBase();
           
            int result = db.RunProc("insert into 配件更换表(任务Flag,配件Flag,更换日期,所属医院) values('" + TaskFlag + "','" + SpareFlag + "','" + Replace_Date + "','" + Hospital + "')");
           
            //配件表配件出库

            if (result == 1)
            {


                DataBase db2 = new DataBase();

                result = db.RunProc("update 配件表 set GOSDate='" + Replace_Date + "' where Flag= '"+ SpareFlag + "'");
            }


            return result.ToString();






        }

        public string Add_Spare_To_Storage(string Flag, string Name, string Type, string SrNr, string Price,string Factory, string NeworOld, string PISDate, string HospitalID,string AddUserID,string etc)
        {

            DataBase db = new DataBase();
            int result = db.RunProc("insert into 配件表(Flag,Name,Type,SrNr,Price,Factory,NeworOld,PISDate,HospitalID,AddUserID,etc) values('" + Flag + "','" + Name + "','" + Type + "','" + SrNr + "','" + Price + "','" + Factory + "','" + NeworOld + "','" + PISDate + "','" + HospitalID + "','" + AddUserID + "','" + etc + "')");
            return result.ToString();



        }




        public DataSet Repair_Log_List(string rp_nmr)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,报修编号,维修记录号,是否有图片,是否有音频,记录内容,记录人,记录时间,记录类型 FROM 维护记录表 WHERE convert(nvarchar(255),报修编号)= '" + rp_nmr + "' order by ID";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }



        public DataSet EQ_Type_List()
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select * FROM 设备类别表 ";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }


        public DataSet Region_List()
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select * FROM 省直辖市表 ";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }







       


        public DataSet Get_Tech_Suport_List_By_EQType(string EQTypeID,string RegionID)
        {
            
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 服务工程师信息表.ID,服务工程师信息表.UserName,服务工程师信息表.ChineseName,服务工程师信息表.备注,服务工程师信息表.服务内容,服务工程师信息表.所属公司,服务工程师信息表.用户等级,服务工程师信息表.好评数量 FROM 服务工程师信息表 Join 服务工程师负责地区表 ON 服务工程师信息表.ID=服务工程师负责地区表.UserID    WHERE  convert(nvarchar(255),服务工程师信息表.设备分类)= '" + EQTypeID + "'AND convert(nvarchar(255),服务工程师负责地区表.ProvinceID)= '" + RegionID + "'  ";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Get_Tech_Suport_List(string DeptID)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,Name,ChineseName, Remark,SkillLevel,Service  FROM Users  WHERE  convert(nvarchar(255),DeptID)= '" + DeptID + "' ";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }



        public DataSet get_HSP_Tech_Suport_List(String HSPID)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select Users.ID,Users.Name,Users.ChineseName FROM Users JOIN RoleUsers ON  convert(nvarchar(255),Users.ID)=convert(nvarchar(255),RoleUsers.UserID) JOIN Depts ON  convert(nvarchar(255),Users.DeptID)=convert(nvarchar(255),Depts.DeptNumber) WHERE convert(nvarchar(255),Depts.ParentID)= '" + HSPID + "' AND (convert(nvarchar(255),RoleUsers.RoleID)= '4' OR convert(nvarchar(255),RoleUsers.RoleID)= '10')";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Serv_HSP_Egn_List(String HSPID)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select Users.ID,Users.ChineseName,Users.Name,Users.Remark FROM Users  WHERE (convert(nvarchar(255),Users.Remark)= '工程师' OR convert(nvarchar(255),Users.Remark)= '总工程师' )";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
   
        public DataSet Serv_EQAdmin_List(String HSPID)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select Users.ID,Users.ChineseName FROM Users JOIN RoleUsers ON  convert(nvarchar(255),Users.ID)=convert(nvarchar(255),RoleUsers.UserID) JOIN Depts ON  convert(nvarchar(255),Users.DeptID)=convert(nvarchar(255),DeptNumber) WHERE convert(nvarchar(255),RoleUsers.RoleID)= '2'  AND convert(nvarchar(255),Depts.ParentID)= '" + HSPID + "'";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public DataSet Get_HSP_EQ_Admin_List(String HSPID)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select Users.ID,Users.Name,Users.ChineseName FROM Users JOIN RoleUsers ON  convert(nvarchar(255),Users.ID)=convert(nvarchar(255),RoleUsers.UserID) JOIN Depts ON  convert(nvarchar(255),Users.DeptID)=convert(nvarchar(255),Depts.DeptNumber) WHERE convert(nvarchar(255),RoleUsers.RoleID)= '2'  AND convert(nvarchar(255),Depts.ParentID)= '" + HSPID + "'";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }




        public DataSet get_Fac_Tech_Suport_List(String HSPID)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select Users.ID,Users.Name,Users.ChineseName FROM Users JOIN RoleUsers ON  convert(nvarchar(255),Users.ID)=convert(nvarchar(255),RoleUsers.UserID) JOIN Depts ON  convert(nvarchar(255),Users.DeptID)=convert(nvarchar(255),Depts.DeptNumber) WHERE convert(nvarchar(255),RoleUsers.RoleID)= '8'  AND convert(nvarchar(255),Depts.ParentID)= '" + HSPID + "'";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }







        public DataSet Get_Depts_List(string PID)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select ID,Name,DeptNumber,PID FROM Depts WHERE convert(nvarchar(255),PID)= '" + PID + "'";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }




        public DataSet Get_Department_List()
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            //string selectStr = "select * from 科室表 where convert(nvarchar(255),hospital)= '" + hospital + "'";
            string selectStr = "select * from 科室表";

            ds = db.RunProcReturnDS(selectStr);
            return ds;
        }

     




        //工程师维修任务表
        public DataSet Engineer_Task_list(string username)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            // string selectStr = "select * from 维修表 where convert(nvarchar(255),报修人)= '" + bxr + "'";
            string selectStr = "select 维修表.报修编号,设备表.设备名称,设备表.资产编号,设备表.所属科室,维修表.维修进程,维修表.是否完成,维修表.报修时间,维修表.故障现象 from 维修表  JOIN 设备表 ON  convert(nvarchar(255),维修表.资产编号)=convert(nvarchar(255),设备表.资产编号) WHERE convert(nvarchar(255),设备表.负责工程师)= '" + username + "' AND convert(nvarchar(255),维修表.是否完成)= '否' AND convert(nvarchar(255),设备表.删除标识)= '0'";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }

        public string DeleteRP(string RPNo)
        {

            DataBase dbs = new DataBase();
            string selectStr = "delete from 任务表 where 报修编号='" + RPNo + "'";
            dbs.RunProc(selectStr);
            dbs.Dispose();
            return "ok";


        }

        public string GetPSW(string Name)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

          
            string selectStr = "select * from Users where convert(nvarchar(255),Name)= '" + Name + "'";

            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)

                return ds.Tables[0].Rows[0][3].ToString().Trim();
            else
                return "nouser";

        }

        public string Get_Android_Reg_Flag(string Name)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select android from Users where convert(nvarchar(255),Name)= '" + Name + "'";

            ds = db.RunProcReturnDS(selectStr);
            

                return ds.Tables[0].Rows[0][0].ToString().Trim();
                

        }



        public string GetDeptm(string Name)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select * from 用户表 where convert(nvarchar(255),用户ID)= '" + Name + "'";

            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)

                return ds.Tables[0].Rows[0][3].ToString().Trim();
            else
                return "0";

        }

     

        public void eBindData(string name, string UserID, string ChanelID)
        {
            DataBase dbs = new DataBase();

            string selectStr = "update 用户表 set UserID='" + UserID + "',ChanelID='" + ChanelID + "'where convert(nvarchar(255),用户ID)='" + name + "'";

            dbs.RunProc(selectStr);

            dbs.Dispose();

        }

        public string Set_EQ_Task(string TaskFlag, string EQFlag)
        {
            DataBase dbs = new DataBase();

            string selectStr = "update 报修任务表 set 设备Flag='" + EQFlag + "' where convert(nvarchar(255),Flag)='" + TaskFlag + "'";

           string result= dbs.RunProc(selectStr).ToString();

            dbs.Dispose();
            return result;
        }

        public int Add_Task_File(string Flag, string FileName, string FileType)
        {
            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into 报修任务文件表 (Flag,文件名,文件类型) values('" + Flag + "','" + FileName + "','" + FileType + "')");

            return i;
        }
        public int Add_WXNr(string EQFlag, string WXNr)
        {
            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString();

            int i = db.RunProc("insert into 编号报修表 (RPNr,EQFlag) values('" + WXNr + "','" + EQFlag + "')");

            return i;
        }

        public int Add_Task_Log_File(string Flag, string FileName, string FileType)
        {
            DataBase db = new DataBase();
            string t = DateTime.Now.ToShortDateString()+" "+DateTime.Now.ToShortTimeString();

            int i = db.RunProc("insert into 报修任务记录文件表 (Flag,文件名,文件类型) values('" + Flag + "','" + FileName + "','" + FileType + "')");

            return i;
        }




        public string DEL_Task(string Flag)
        {
            string zxr = "";
            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select * from 报修任务表 where convert(nvarchar(255),Flag)= '" + Flag + "'";

            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)

                zxr=ds.Tables[0].Rows[0][9].ToString().Trim();


            if (zxr != "0")
            {
                DataBase dbs = new DataBase();
                string selectStr2 = "DELETE  FROM 报修任务表  where convert(nvarchar(255),Flag)= '" + Flag + "'";


                return dbs.RunProc(selectStr2).ToString();

            }
            else
                return "0";
                    
        }








        public string Get_Task_Discribe(string TSKFLag)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 故障描述 from 报修任务表 where convert(nvarchar(255),Flag)= '" + TSKFLag + "' ";

            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0][0].ToString();
            else
                return "无";

        }

        public string Get_Task_Log_Discribe(string TSKFLag)
        {

            DataBase db = new DataBase();

            DataSet ds = new DataSet();


            string selectStr = "select 记录内容 from 报修任务记录表 where convert(nvarchar(255),Flag)= '" + TSKFLag + "' ";

            ds = db.RunProcReturnDS(selectStr);
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0][0].ToString();
            else
                return "无";

        }


        public string Add_User(string UserName, string Password, string ChineseName, string EnglishName, string DeptID,string Role)
        {
            string userid;
            DataSet ds2;
            DataBase db2 = new DataBase();
            DataSet ds4;
            DataBase db4 = new DataBase();
            string selectStr = "select ID from Users where convert(nvarchar(255),Name)= '" + UserName + "'";

            ds2 = db2.RunProcReturnDS(selectStr);
            if (ds2.Tables[0].Rows.Count > 0)
                return "3";


            selectStr = "select ChineseName from Users where convert(nvarchar(255),ChineseName)= '" + ChineseName + "'";

            ds4 = db4.RunProcReturnDS(selectStr);
            if (ds4.Tables[0].Rows.Count > 0)
                return "4";


            else
            {
                DataBase db = new DataBase();


                int i = db.RunProc("insert into Users (Name,  Password,  ChineseName,  EnglishName,  DeptID) values('" + UserName + "','" + PasswordUtil.CreateDbPassword(Password) + "','" + ChineseName + "','" + EnglishName + "','" + DeptID + "')");
                
                if (i == 1)
                {

                    DataBase db3 = new DataBase();
                    string selectStr33 = "select ID from Users where convert(nvarchar(255),Name)= '" + UserName + "'";

                    ds2 = db2.RunProcReturnDS(selectStr33);
                    userid = ds2.Tables[0].Rows[0][0].ToString();
                    db.RunProc("insert into RoleUsers (RoleID,UserID) values('" + Role + "','" + userid + "')");

                    return userid;



                }
                else
                    return "0";




            }

        }



/// <summary>
/// ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/// </summary>


        public string SendSMS(string phonenr, string deptname, string eqname)
        {
            Util.SetCertificatePolicy();
            String url = "https://api.netease.im/sms/sendtemplate.action";
            url += "?templateid=7408&mobiles=['"  +   phonenr    +"']&params=['"  +  deptname  +  "','" + eqname +"']";//TO开发者：accid填要创建的id

            String appKey = "5d1fd1568f14f138a75ce1ead82f47b3";//TO开发者：假数据，具体参考开发文档
            String appSecret = "122d5e94051d";//TO开发者：假数据，具体参考开发文档
            String nonce = "12345";//TO开发者：具体参考开发文档

            TimeSpan ts = DateTime.Now.ToUniversalTime() - new DateTime(1970, 1, 1);
            Int32 ticks = System.Convert.ToInt32(ts.TotalSeconds);
            String curTime = ticks.ToString();
            String checkSum = CheckSumBuilder.getCheckSum(appSecret, nonce, curTime);

            IDictionary<object, String> headers = new Dictionary<object, String>();
            headers["AppKey"] = appKey;
            headers["Nonce"] = nonce;
            headers["CurTime"] = curTime;
            headers["CheckSum"] = checkSum;
            headers["ContentType"] = "application/x-www-form-urlencoded;charset=utf-8";
            return HttpClient.HttpPost(url, null, headers);
        }


        class CheckSumBuilder
        {
            // 计算并获取CheckSum
            public static String getCheckSum(String appSecret, String nonce, String curTime)
            {
                byte[] data = Encoding.Default.GetBytes(appSecret + nonce + curTime);
                byte[] result;

                SHA1 sha = new SHA1CryptoServiceProvider();
                // This is one implementation of the abstract class SHA1.
                result = sha.ComputeHash(data);

                return getFormattedText(result);
            }

            // 计算并获取md5值
            public static String getMD5(String requestBody)
            {
                if (requestBody == null)
                    return null;

                // Create a new instance of the MD5CryptoServiceProvider object.
                MD5 md5Hasher = MD5.Create();

                // Convert the input string to a byte array and compute the hash.
                byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(requestBody));

                // Create a new Stringbuilder to collect the bytes
                // and create a string.
                StringBuilder sBuilder = new StringBuilder();

                // Loop through each byte of the hashed data 
                // and format each one as a hexadecimal string.
                for (int i = 0; i < data.Length; i++)
                {
                    sBuilder.Append(data[i].ToString("x2"));
                }

                // Return the hexadecimal string.
                return getFormattedText(Encoding.Default.GetBytes(sBuilder.ToString()));
            }

            private static String getFormattedText(byte[] bytes)
            {
                char[] HEX_DIGITS = { '0', '1', '2', '3', '4', '5',
            '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f' };
                int len = bytes.Length;
                StringBuilder buf = new StringBuilder(len * 2);
                for (int j = 0; j < len; j++)
                {
                    buf.Append(HEX_DIGITS[(bytes[j] >> 4) & 0x0f]);
                    buf.Append(HEX_DIGITS[bytes[j] & 0x0f]);
                }
                return buf.ToString();
            }
        }

        class HttpClient
        {
            public static string HttpPost(string url, Stream data, IDictionary<object, string> headers)
            {
                System.Net.WebRequest request = HttpWebRequest.Create(url);
                request.Method = "POST";
                if (data != null)
                    request.ContentLength = data.Length;
                request.ContentType = "application/x-www-form-urlencoded;charset=utf-8";

                if (headers != null)
                {
                    foreach (var v in headers)
                    {
                        if (v.Key is HttpRequestHeader)
                            request.Headers[(HttpRequestHeader)v.Key] = v.Value;
                        else
                            request.Headers[v.Key.ToString()] = v.Value;
                    }
                }
                HttpWebResponse response = null;
                try
                {
                    // Get the response.
                    response = (HttpWebResponse)request.GetResponse();
                    // Display the status.
                   // Console.WriteLine(response.StatusDescription);
                    // Get the stream containing content returned by the server.
                    Stream dataStream = response.GetResponseStream();
                    // Open the stream using a StreamReader for easy access.
                    StreamReader reader = new StreamReader(dataStream, System.Text.Encoding.Default);
                    // Read the content.
                    string responseFromServer = reader.ReadToEnd();
                    // Display the content.
                    return responseFromServer;


                }
                catch (Exception e)
                {
                   // Console.WriteLine(e.Message);
                    //Console.WriteLine(response.StatusDescription);
                    return e.Message ;
                }



            }
        }
    }


}
